Multiple Sheet merge - VBA in Excel 2010

[ShilP]

Hi GuysI have a workbook with multiple worksheets:Sheet 2Sheet 3Sheet 4Sheet 5Sheet 6Sheet 7Sheet 8Sheet 9Sheet 11Sheet 12Sheet 13Region1Region2Region3Region4How can I add All the data in Sheet 2 into a new worksheet called “Master”, and subsequently, add all the data in the remaining worksheets underneath the data which was just placed in the Master sheet.ThanksShil

 

[ShilP]

think i need to work out how to create a line break in my post also.

 

[My Aswer Is This]

So you want to take all the data in all your sheets and put all that data into the Master sheet is that correct?

 

[ShilP]

Yup, thats correct

 

[My Aswer Is This]

Are all of the sheets setup the same way with the same number of columns.
Can you tell us what columns need to be copied over for example would it always be columns 1 to 6

 

[ShilP]

Yes, they are all the same. the columns i would need are 1 to 17thanksShil

 

[WarPigl3t]

Start by creating a sheet named "Master" and then run this macro. The macro does not create a sheet called "Master" for you. Do it manually. Also you didn't specify if you had headers so I assumed you didn't. If you have headers, just change everywhere that you see "A1" and make it "A2".

Sub myMacro()
master = "Master"
lastRow = Sheets("Sheet 2").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet 2").Range("A1:Z" & lastRow).Copy
ActiveSheet.Paste Destination:= Sheets(master).Range("A1")
CutCopyMode = False

For Each wksht in Workbooks
If wksht <> "Sheet 2" Then
nextRow = Sheets(master).Range("A" & Rows.Count).End(xlUp).Row + 1
lastRow = Sheets(wksht).Range("A" & Rows.Count).End(xlUp).Row
Sheets(wksht).Range("A1:Z" & lastRow).Copy
ActiveSheet.Paste Destination:= Sheets(master).Range("A" & next row)
CutCopyMode = False
End If
Next wksht
End Sub

 

[ShilP]

Start by creating a sheet named "Master" and then run this macro. The macro does not create a sheet called "Master" for you. Do it manually. Also you didn't specify if you had headers so I assumed you didn't. If you have headers, just change everywhere that you see "A1" and make it "A2".Sub myMacro()master = "Master"lastRow = Sheets("Sheet 2").Range("A" & Rows.Count).End(xlUp).RowSheets("Sheet 2").Range("A1:Z" & lastRow).CopyActiveSheet.Paste Destination:= Sheets(master).Range("A1")CutCopyMode = FalseFor Each wksht in WorkbooksIf wksht <> "Sheet 2" ThennextRow = Sheets(master).Range("A" & Rows.Count).End(xlUp).Row + 1lastRow = Sheets(wksht).Range("A" & Rows.Count).End(xlUp).RowSheets(wksht).Range("A1:Z" & lastRow).CopyActiveSheet.Paste Destination:= Sheets(master).Range("A" & next row)CutCopyMode = FalseEnd IfNext wkshtEnd Sub

Hi, thanks. Its giving me a syntax error at the following line:ActiveSheet.Paste Destination:= Sheets(Master).Range("A" & next row)Any idea how i can fix this?

 

[My Aswer Is This]

Shilp. Did you create a sheet named "Master"?

Change: (Master).Range("A" & next row)
To:

(Master).Range("A" & nextRow)

 

[ShilP]

Shilp. Did you create a sheet named "Master"?Change: (Master).Range("A" & next row)ToMaster).Range("A" & nextRow)

Thanks, this worked for the first Sheet. Now how can i edit this so that all Sheets Data appear underneath the master data sheet. Currently your one only shows only one worksheets worth of data. ThanksShil