Selected Answer
Hello again speed,
You are close but there are a couple of changes needed. You say in your post you want to copy the last 10 rows on Sheet1 to Sheet2 but what you have written, your code copies from Sheet2 to Sheet1 - opposite of what you want.
You don't indicate if you want these 10 rows to replace the existing Sheet2 rows or to copy these 10 rows below the existing. So I have written code for both options.
Sub Copy_Last10_Rows_Replace()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim LastRow As Long
Dim Last10Rows As Long
Set sht1 = ThisWorkbook.Sheets("Sheet1")
Set sht2 = ThisWorkbook.Sheets("Sheet2")
LastRow = sht1.Range("A" & Rows.Count).End(xlUp).Row
Last10Rows = sht2.Range("A" & Rows.Count).End(xlUp).Row
' copy last 10 rows from sht1 to sht2 and replace existing on sht2
sht1.Range(LastRow - 9 & ":" & LastRow).Copy sht2.Range("2:11")
End Sub
If you want to add the sht1 data to the existing sht2 data then use the following:
Sub Copy_Last10_Rows_Add()
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim LastRow As Long
Dim Last10Rows As Long
Set sht1 = ThisWorkbook.Sheets("Sheet1")
Set sht2 = ThisWorkbook.Sheets("Sheet2")
LastRow = sht1.Range("A" & Rows.Count).End(xlUp).Row
Last10Rows = sht2.Range("A" & Rows.Count).End(xlUp).Row
' copy last 10 rows from sht1 to sht2 below existing data on sht2
sht1.Range(LastRow - 9 & ":" & LastRow).Copy sht2.Range(Last10Rows + 1 & ":" & Last10Rows + 1)
End Sub
If this solves your issue please mark my answer as Selected.
Cheers :-)
