Recording a macro
If you want the first 10 visible rows in the Autofiltered List
Sub ABC()
Dim rng As Range, cell As Range
Dim cnt As Long
Set rng = ActiveSheet.AutoFilter.Range.Columns(1).Cells
cnt = 0
For Each cell In rng
If cell.Row < 1 Then
If cell.EntireRow.Hidden = False Then
cnt = cnt + 1
End If
End If
If cnt = 10 Then
rng.Resize(cell.Row).Copy Destination:= _
Worksheets("Sheet2").Range("A1")
Exit For
End If
Next
End Sub
If you use a filter of "Top 10" so you want to copy all visible rows then it
would just be:
Sub ABC()
Activesheet.AutoFilter.Range.Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End Sub
--
Regards,
Tom Ogilvy
"Ant" wrote in message
...
I am trying to select the top 10 rows of a filtered list and then paste
the
values into a temporary worksheet. I have tried several ways of recording
a
macro that will do this but without success.
Currently the line of code that is selecting the range reads
"Range("A1:E11").Select". What should this read to always be the top ten
rows of a filtered list?
Thanks
Tony
|