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

Option Explicit

Sub CopyFiltered()
Dim ws As Worksheet
Dim wb As Workbook
Dim source As Range

Set ws = ActiveSheet
Set source = ws.Cells.SpecialCells(xlCellTypeVisible)
source.Copy
Set wb = Workbooks.Add
Set ws = wb.ActiveSheet
ws.Range("A1").PasteSpecial xlPasteValues
End Sub
"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

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