How to copy the last N rows of data?

Question

I have VBA code which 'mirrors'/copies a specific range of cells from another workbook/sheet. The range of cells are row after row of test results.

Is there a way to copy the last 10 (or however many) rows of data, instead of a specific range?

Set Dat1 = Workbooks("Test Results.xlsm").Worksheets("Example").Range("AP31:AP15000") 

Raw1.Range("A3:A15000").Resize(Dat1.Rows.Count, Dat1.Columns.Count).Cells.Value = Dat1.Cells.Value

The first line of code sets Dat1 as the data AP31:AP15000 from the test result file.

The second line is basically saying put the value of Dat1 into Raw1 (which is my destination).

Can I use this method for say selecting the last 20 rows or do I have to use a different method?

Answer 1

I'd suggest to check which range you need to copy and use Offset() and Resize():

Sub Test()

Dim wb As Workbook: Set wb = Workbooks("Test Results.xlsm")
Dim ws As Worksheet: Set ws = wb.Worksheets("Example")
Dim rng As Range, lr As Long

lr = ws.Cells(ws.Rows.Count, 42).End(xlUp).Row
If lr > 11 Then
    Set rng = ws.Cells(lr, 42).Offset(-9).Resize(10)
ElseIf lr > 2 Then
    Set rng = ws.Range(ws.Cells(3, 42), ws.Cells(lr, 42))
End If

'Do whatever with the "rng" variable.

End Sub