Copy X Rows

SAMPLE FILE: CopyXRows.xls


"I have a filtered set of data, I only want to copy the first 10 rows of the remaining visible data to another sheet. How can I do that?"


  1. An AutoFilter is active on the sheet already
  2. The number of rows is easily editable (colored in the code below to draw attention)
Option ExplicitSub Copy10Rows()'Author: Jerry Beaucaire,'Date: 10/22/2010'Summary: Only copies 10 visible rows to new sheetDim LR As LongDim Rw As LongDim RwTenth As Long
LR = Range("A" & Rows.Count).End(xlUp).RowRw = 2 Do If Rows(Rw).Hidden = False Then RwTenth = RwTenth + 1 If RwTenth = 10 Then Exit Do Rw = Rw + 1 LoopRange("A2:A" & Rw).EntireRow.Copy _ Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)End Sub

From the website comes this alternate technique that does the same thing without "looping".

Sub Copy10RowsDomenic()'Author: Domenic,'Date: 10/22/2010'Summary: Only copies 10 visible rows to new sheet, no loop version'' LR As LongDim Rw As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row Rw = Application.Evaluate("SMALL(IF(SUBTOTAL(3,OFFSET(A2:A" & LR & ",ROW(A2:A" & _ LR & ")-ROW(A2),0,1)),ROW(A2:A" & LR & ")),MIN(SUBTOTAL(3,A2:A" & LR & "),10))")Range("A2:A" & Rw).EntireRow.Copy _ Sheets("Sheet2").Range("A" & Sheets("Sheet2").Rows.Count).End(xlUp).Offset(1) End Sub