Saving a filter?

R

rik84

I am sorting a large spreadsheet. I have filtered down to my chosen rows, now
I want to save this as a separate sheet!
 
J

Jim May

Or use (in a standard module) - **chg "Sheet2" name below, if necessary**:
Code By Tom Ogilvy (If I remember right)

Sub CopyFilter()
Dim rng As Range
Dim rng2 As Range
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No Data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Copy Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData
End Sub

Hope this helps
 
Top