I recently had a question regarding copying data after a filter had been applied. What if we wanted the top n items from a list to be copied to another place. The problem to address here is that a filtered list can have cells which are hundreds or thousands of rows apart. So copying sequentially might not work. Also, you may not want to Filter by top n. So selecting the top n visible cells in a filtered list becomes a bit problematic. I suppose the simple solution is to copy that filtered list to a new location - only the filtered items will appear and they will be sequential. The following procedure counts the rows from 1 to 10 in a filtered list and copies the results and places them on Sheet 2 in cells A2.
Sub TopNRows()
Dim i As Long
Dim r As Range
Dim rWC As Range
Set r = Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12)
For Each rwC In r
i = i + 1
If i = 10 Or i = r.Count Then Exit For
Next rwC
Range(r(1), rwC).Resize(, 12).SpecialCells(12).Copy sheet2.[A2]
End Sub
The procedure runs on the active sheet and will paste data to the worksheet code name (sheet2). Be sure to check that you actually have a sheet 2 in the VBA editor if you are going to use this procedure. The attached file should help crystalise the procedure. Also the procedure is for the first 12 columns (resize, 12) is to create a larger area to copy.
Enjoy
