Combining sheets

G

New Member
Joined
Aug 19, 2002
Messages
41
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

hedrijw

Board Regular
Joined
Jun 17, 2002
Messages
210
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

New Member
Joined
Aug 19, 2002
Messages
41
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

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
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
 

Forum statistics

Threads
1,147,823
Messages
5,743,406
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top