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

I have a example on this page Mac
http://www.rondebruin.nl/tips.htm


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Mac" wrote in message ...
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

--
Message posted from http://www.ExcelForum.com

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




-----Original Message-----
Hi Mac
Try using this code. You may have to modify the cell
references to suit
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 Sub


---
Message posted from http://www.ExcelForum.com/

.