Copying a variable nuimber of rows
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 |
Copying a variable nuimber of rows
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 |
Copying a variable nuimber of rows
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 |
Copying a variable nuimber of rows
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 |
All times are GMT +1. The time now is 03:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com