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

Try

With Activesheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
if LastRow 10 Then

FirstRow = LastRow - 9
Else

FirstRow = 1
EndIf

.Rows(FirstRow).Resize(LastRow - FirstRow).Copy
'then paste it wherever
End With

--

HTH

Bob

"FinMan@Sussex" wrote in message
...
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

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

Just out of curiosity, what would be the problem in copying blank lines
along with your data lines (in the case when there were less than 10
completed lines)?

--
Rick (MVP - Excel)



"FinMan@Sussex" wrote in message
...
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