Copy last row of a sheet and paste in last row of a different sheet VBA

Question

Right now I have a Workbook containing a master sheet and multiple individual customer sheets. I am writing some code to look at the customer column, copy the row and then paste it in their respective sheet. At the end I want the last row from my template sheet to be pasted as the last row for the customer sheet. This is to calculate averages. So far it works but the last row gets pasted to the top of the sheet but not the bottom. I cant figure out how to get it to be the last row.

Sub copyPasteDataCustomer()
Dim sws As Worksheet
Dim tws As Worksheet
Dim cel As Range
Set sws = Sheets("Master")
For Each cel In sws.Range("B5:B" & Range("B" & Rows.Count).End(xlUp).Row)
    Set tws = Sheets(CStr(cel.Value))
    cel.EntireRow.Copy tws.Range("A" & Rows.Count).End(xlUp).Offset(1)

    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Canvus")
    Dim ws2 As Worksheet: Set ws2 = tws
    For i = 2 To ws1.Range("G" & Rows.Count).End(xlUp).Row
        ws1.Rows(i).Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, "G").End(xlUp))
    Next i
Next cel
End Sub

Is the line pasting to the top of the for the one in your For i loop? You may also need to add .Row after .End(xlUp) in that loop to make sure it's getting the row properly. — BruceWayne
Yes that is correct the line pasting on the top is in the loop. And thank you I will try adding that now. @BruceWayne — KingSneaky
Oh, try ...Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, "G").End(xlUp).Row + 1). Also, why create another Worksheet variable, and not just use tws instead of ws2? — BruceWayne
@BruceWayne that is close but it ends up getting both my last and top row placing that on the bottom. I had two separate variables because at first I didn't have that sheet in my for loop. — KingSneaky
"...but it ends up getting both my last and top row placing that on the bottom" - so it's copying two rows? Step through the code with F8 and see if you can pinpoint where/why it's not just pasting one row to the end each time. — BruceWayne

KingSneaky KingSneaky · Accepted Answer · 2017-08-08T14:35:22

I ended up deleting the rest of the canvas template to just have the bottom row on the sheet. Works awesome now.

Copy last row of a sheet and paste in last row of a different sheet VBA

1 Answers