Prev: How to turn off standard control functions
Next: Conditional formating of cells
From: FinMan on 11 May 2010 06:09
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
From: Bob Phillips on 11 May 2010 06:21
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(a)Sussex" <FinMan(a)Sussex@discussions.microsoft.com> wrote in message
news:3CAC20D0-1A73-467B-8C69-59CB3B86659C(a)microsoft.com...
> 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


From: Mike H on 11 May 2010 06:29
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(a)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
From: Rick Rothstein on 11 May 2010 13:26
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(a)Sussex" <FinMan(a)Sussex@discussions.microsoft.com> wrote in message
news:3CAC20D0-1A73-467B-8C69-59CB3B86659C(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: How to turn off standard control functions
Next: Conditional formating of cells