Solved: Last 10 Rows of a Range from a Closed Workbook

[AMRG2013]

The code below copies Range (D13:Q100) of workbook2.xls to the workbook1.xls (this workbook), in Range (D13:Q100). And it works fine!
What I want is to copy only the last 10 rows of workbook2.xls, within that range (D13:Q100), to this workbook1.xls.
How can I do this?
thank you


Sub GetDataFromClosedWorkbook()
Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("D\Work\Workbook2.xls", True, True)
' open the source workbook, read only
With ThisWorkbook.Worksheets("Results")
' read data from the source workbook
.Range("D13:100").Formula = wb.Worksheets("Data").Range("D13:Q100").Formula
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub

[p45cal]

Try this (not fully tested):Sub GetDataFromClosedWorkbook()
Dim wb As Workbook, SourceRng As Range
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("D\Work\Workbook2.xls", True, True)
' open the source workbook, read only
Dim lr As Long
With wb.Sheets("Data")
lr = TheLastRow(.Range("D13:Q100"))
If lr > 0 Then
Set SourceRng = Intersect(.Range("D" & lr - 9).Resize(10, 14), .Range("D13:Q100")) 'in case there are fewer than 10 rows to copy.
With ThisWorkbook.Worksheets("Results")
' read data from the source workbook
.Range("D13").Resize(SourceRng.Rows.Count, SourceRng.Columns.Count).Formula = SourceRng.Formula
'.Range("D13").Resize(SourceRng.Rows.Count, SourceRng.Columns.Count).Value = SourceRng.Value
End With
End If
End With
wb.Close False ' close the source workbook without saving any changes
Set wb = Nothing ' free memory
Application.ScreenUpdating = True ' turn on the screen updating
End Sub


Function TheLastRow(rng)
On Error Resume Next
TheLastRow = rng.Find(what:="*", After:=rng.Cells(1), lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
On Error GoTo 0
End Function
There is a commented-out line starting:
'.Range("D13").Resize…
which might be safer to use than the line above it.

[AMRG2013]

Your code works fine!
Thank You very much for your attention, and the help. Again...

My regards,
AMRG2013