Merging 3 Sheets Into 1 Sheet

Question

I want a script that pulls 3 different worksheets from another workbook and just stack the data in a new blank sheet.

This seems like it should work but it's not:

Sub CombineSheets()

Set NewSheet = Worksheets("Sheet2")
Set MC = Workbooks.Open("S:\OtherWorkBook.xlsm")
Set T1 = MC.Worksheets("T1")
Set T2 = MC.Worksheets("T2")
Set T3 = MC.Worksheets("T3")

With T1
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A5:I" & lastrow).Copy NewSheet.Range("A" & wks.Rows.Count).End(xlUp)
End With

With T2
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A5:I" & lastrow).Copy NewSheet.Range("A" & wks.Rows.Count).End(xlUp)
End With

With T3
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A5:I" & lastrow).Copy NewSheet.Range("A" & wks.Rows.Count).End(xlUp)
End With

Workbooks("OtherWorkBook.xlsm").Close SaveChanges:=False

End Sub

The script runs but nothing is dumped into NewSheet? What am I missing. Thank you!

You will save yourself a world of pain if you declare all of your variables, and force yourself to do so as explained here: http://stackoverflow.com/q/1139321/119775 — Jean-François Corbett, Jun 19 '14 at 12:39

score 3 · Accepted Answer · answered Jun 19 '14 at 12:35

You are missing Destination:= after your .Copy call.

With T1
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A5:I" & lastrow).Copy Destination:=NewSheet.Range("A" & NewSheet.Rows.Count).End(xlUp)
End With

This worked for me. I also changed wks to NewSheet. because your code doesn't clarify what wks exactly is.

Merging 3 Sheets Into 1 Sheet

1 Answers1