Hi,

Try this, you need to change srcsht and dstsht to the correct sheet names

Sub copyrows()
Dim LastRow As Long
Set SrcSht = Sheets("Sheet1")
Set DstSht = Sheets("Sheet2")
LastRow = SrcSht.UsedRange.SpecialCells(xlCellTypeLastCell). Row
SrcSht.Rows(WorksheetFunction.Max(2, LastRow - 9) & ":" & LastRow).Copy _
Destination:=DstSht.Range("A1")
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"FinMan@Sussex" wrote:

I want to copy the most recent 10 rows (excluding the header row) from
worksheet A to worksheet B. My problem is how do I define a variable number
of rows if there are fewer than 10 lines completed? I do not want to copy
any blank lines. I am fairly new to VBA and am struggling to work out the
code for this so any help will be gratefully appreciated. John