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

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


<(E-Mail Removed)> wrote in message news:cfc5c03f-7a0d-4205-8cbc-(E-Mail Removed)...
> 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

Thanks.

I'll try it out on Monday.