Combining sheets

[G]

If you have 20 sheets of data in 1 workbook, how can you combine all the data into 1 sheet with out losing your other 20 sheets and with out copy and past method.

Thanks in advance,
G

 

[hedrijw]

Hre's a quick and dirty method:

Assuming the first 20 sheets in the book are the ones you want to copy and the sheet you want to copy to is named "ALL SHEETS":

Sub Copy_All_Sheets()
Sheets("ALL SHEETS").Select
Cells.Select
Selection.ClearContents
[a1].Select
where = [a1].Address
For i = 1 To 20
Worksheets(i).UsedRange.copy Destination:=Worksheets("All Sheets").Range(where)
where = [a65536].End(xlUp).Offset(1, 0).Address
Next
End Sub

I'm using Excel 2002

 

[G]

Thank you! I will give this a try, quick question is this a Macro? I have only recorded Macros, could this work to edit directly in Visual Basics?

 

[WillR]

Yep,

Just paste the code into a module in the visual basic editor my friend...

(i.e. the text from SUB to END SUB)
_________________
Will<img src=http://www.smilies.nl/dieren/pengy.gif>
Do NOT feed the Penguin!
This message was edited by WillR on 2002-09-06 05:04