Filtered Range.

[Nigel Martin]

Is there any way of having a macro copy an auto filtered range to a new worksheet then deleting its original data and saving itself as a new file? Thanks in advance.

 

[BrianB]

Use Data/Filter/Advanced Filter (?) and macro record the process.

Post a new message if you get probs.

 

[Andrew Poulsom]

Something like this?

Sub Test()
    Dim Sh As Worksheet
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim WB As Workbook
    Set Sh = Worksheets("Sheet1")
    Set Rng1 = Sh.Range("A1:A" & Sh.Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    Set Rng2 = Sh.Range("A2:A" & Sh.Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    Set WB = Workbooks.Add
    Rng1.Copy
    With WB
        .Sheets(1).Range("A1").PasteSpecial xlPasteValues
        .SaveAs "NewFile.xls"
        .Close
    End With
    Rng2.EntireRow.Delete
    Rng1.AutoFilter
End Sub