How to append/combine (not add) data in several sheets into one?

[ParkCrescent]

Assume that you have 100 sheets in an Xcel workbook which each has 10 rows of
data and you want 1 sheet with 1000 rows of data, what is the easiest way to
do it?

Is there an easier way to do it than cutting and pasting 99 times?

 

[Monte]

On the new worksheet, create a formula returning the value in the first cell
of the first worksheet, then copy paste ten rows down and however many
columns wide.

Copy the area you just pasted, go to the next empty row and paste. While the
new area is still selected Find/Replace (Ctrl+H) the name of the first
worksheet with the next worksheet name. Spelling counts.

Repeat until all data from the old worksheets appear on the same page,
highlight the entire page, copy/paste values and delete the old worksheets.


:
Assume that you have 100 sheets in an Xcel workbook which each has 10 rows
of data and you want 1 sheet with 1000 rows of data, what is the easiest
way to do it?

Is there an easier way to do it than cutting and pasting 99 times?

 

[Dave Peterson]

Ron de Bruin has sample code at:
http://www.rondebruin.nl/copy2.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm