Adding all the data to one sheet

  • Thread starter Thread starter Mac
  • Start date Start date
M

Mac

Hi all,
I have several sheets in one file. All the sheets are containing the
same columns but different data. I need to copy all the data of each
sheet to one master sheet for analysis. How to do it? I mean master
sheet can have data from 1st sheet then without leaving any blank
rows, data from the 2nd sheet to be copied then without leaving any
blank rows data from 3rd sheet to be copied and so on for around 34
sheets. I think I am clear what I need.

Thanks for any help.
Raja
 
Hi Mac
Try using this code. You may have to modify the cell references to sui
your data.

Sub CopySheets()
Dim MySheet As Integer
For MySheet = 2 To 34
Sheets(MySheet).Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Next MySheet
End Su
 
Good attempt. You don't need to activate or select sheets
or ranges in order to use them. For example your code
becomes..

Sub CopySheets()
Dim MySheet As Long
For MySheet = 2 To 34
With Worksheets(MySheet)
.Range(.Range("A2"), _
.Range("A2").End(xlToRight).End(xlDown)).Copy
Worksheets(1).Range("A65000").End(xlUp).Offset
(1, 0).PasteSpecial xlAll
End With
Next MySheet
End Sub

Two issues here
One is that your columns are not defined. So if the
tables are of different widths, the your code will bring
everything in.

.Range(.Range("A2"), _
.Range("G2")xlToRight).End(xlDown)).Copy
This adjustment will only copy columns A to G

Furthermore, using a numeric index for the worksheets is
ok so long as one rembers that Excel numbers the sheets
according to the tab position...so "sheet1" isn't always
going to be worksheets(1)

patrick olloy
Microsoft Excel MVP
 
Back
Top