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

 

[My Aswer Is This]

I did not write this script but I think this would correct the script:

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" & nextRow)
 CutCopyMode = False
nextRow = Sheets(master).Range("A" & Rows.Count).End(xlUp).Row + 1

 End If
 Next wksht
 End Sub

 

[ShilP]

Hi, this only pastes that data on sheet 2, does anyone know how to get the other sheets copied over?thanksShil

 

[WarPigl3t]

The reason why it only posts data from one sheet is because you did not use all of the code I wrote. Here it is again.

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" & nextRow)
CutCopyMode = False
End If
Next wksht
End Sub

 

[ShilP]

Ok, i copied and pasted your code war pig, and again it only copied the first worksheet. Should i be executing this macro whilst in the first worksheet (The new one called master?)Just trying to see why its not working. Thanks

 

[WarPigl3t]

Sorry but I don't see anything wrong with the code. Try running the code line by line using the F8 key. After each line, hover over each variable and see what they equal. Imagine and anticipate what is suppose to happen. That way you can figure out what is wrong. I'm guessing that it is never entering the if statement that says if wksht <> "Sheet 2".

 

[My Aswer Is This]

Are you sure your sheets are name "Sheet 2") or should it be "Sheet2"??
And are you launching this script from "Sheet2"

Try using this script which activates "Sheet2" at the beginning.

Sub myMacro()

Sheets("Sheet2").Activate
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" & nextRow)
CutCopyMode = False
End If
Next wksht
End Sub

 

[ShilP]

yes i can confirm i tried both ways, "Sheet2" and "Sheet 2" and edited the code accordingly. i activated it from Sheet 2. It seems to copy Sheet2's data but not the remaining 13 worksheets. Anyone have any ideas?Thanks

 

[ShilP]

yes i can confirm i tried both ways, "Sheet2" and "Sheet 2" and edited the code accordingly. i activated it from Sheet 2. It seems to copy Sheet2's data but not the remaining 13 worksheets. Anyone have any ideas?Thanks

Hi GuysAnyone know how to do the above? Just trying to refresh this so i can try and find a work aroundThanks

 

[Ombir]

Insert new module of VB editor in your Workbook and paste this code.

Sub mergesheet()
Dim sht As Worksheet
Dim master As Worksheet
Set master = Worksheets.Add(after:=Worksheets(Worksheets.Count))
master.Name = "Master"
Set sht = Worksheets(1)
sht.Range("a1:q1").Copy Destination:=master.Range("a1")
For Each sht In Worksheets
If sht.Index = Worksheets.Count Then
Exit For
End If
lastrow = sht.Cells(Rows.Count, 1).End(xlUp).Row
nextrow = master.Cells(Rows.Count, 1).End(xlUp).Row + 1
sht.Range("A2:Q" & lastrow).Copy Destination:=Range("A" & nextrow)
Next sht
End Sub

Hope this helps !