How to copy from each sheet and paste to a summary sheet

[gordeos]

Hi,

New user needs some help, please.
I have a workbook with 197 sheets. All the 197 sheets have the same
format (2 columns and 121 rows). I would like to copy data from each
sheet to a summary sheet ("Sheet1") to make a long list.
I don't know how to move to the next sheet in the For Next Loop.

Here is the code to copy and paste one sheet at a time:

Sheets("2741").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:B121").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Range("A122").Select
Selection.End(xlDown).Select
Range("A241").Select
Sheets("2743").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:B121").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Range("A242").Select
Selection.End(xlDown).Select
Range("A361").Select
Sheets("2744").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A2:B121").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste


Thanks for helping

 

[Ron de Bruin]

See
http://www.rondebruin.nl/copy2.htm

 

[gordeos]

Thanks.

I'll try it out on Monday.