Advanced Filter Results to a new worksheet

[Gavona]

I want to use Excel advanced filter to extract relevant rows and columns from my database and place the results on a new sheet.

So far I have the code reproduced at the end of this question. Essentially it filters in place then copies visible cells to the new worksheet. I could slightly develop this to include an output range (so I can I dentify only the columns I need) but is there a way I can put this on a new sheet, thus preserving column formats etc. and allowing me to output to a pre-prepared sheet?

My existing code (in case anyone can offer suggestions for improvements) is:

Private Sub FilterGeneric(MyCriteria, mysort1, mysort2, mysort3)
' Creates a new sheet in the current workbook containing sorted and filtered (extracted) values

' Operates on the "Alldata" range in the current workbook

' Parameter 1   = MyCriteria  - must be the name of the range containing the advanced filter criteria
'                             this range must be on a sheet called "Criteria" in the current workbook
'                  MyCriteria - is also used to define the sheetname and to name the extracted data
' Parameters 2-4 = mysort?    - defines the order in which the extracted data will be sorted
'
    Application.Goto Reference:="alldata"
'    ActiveSheet.ShowAllData                     'because hidden columns can make the following fail "too complex"

    Range("Alldata").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Criteria").Range(MyCriteria), Unique:=False

    Application.Goto Reference:="alldata"
    
    Selection.SpecialCells(xlCellTypeVisible).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets.Add
    ActiveSheet.Paste
    ActiveSheet.Name = MyCriteria
    
    With Selection
        .Name = MyCriteria + "Data"
        .CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
            False
 '       .Sort Key1:=Range(mysort1), Order1:=xlAscending, Key2:=Range(mysort2 _
        ), Order2:=xlAscending, Key3:=Range(mysort3), Order3:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  '        .Sort Key1:=Range("ServicePlan"), Order1:=xlAscending, Key2:=Range("SD4" _
        ), Order2:=xlAscending, Key3:=Range("ExcessCommitment£"), Order3:=xlDescending, Header _
        :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
          .Sort _
            Key1:=Range("ServicePlan"), Order1:=xlAscending, _
            Key2:=Range("SD4"), Order2:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    End With
    Application.Run "_resMacros.xls!Footer"
End Sub

Thanks
Gavin
(Excel 2000)

 

[DaleWatson123321]

Hi Gavin,

As one who has used Excel's "Advanced Filter" functions extensively, I'm confident I can provide the help you're seeking.

From the information you've posted, there are a couple of points I would make...

1) You should create your separate sheet in advance - independent from your extraction routine - i.e. the "pre-prepared" sheet you referred to.

2) There is no need to filter the data in place and then copy the filtered records to the separate sheet. You can simply use code to extract the data to the separate sheet. And you can extract ONLY the fields you want.

I believe the "best" way I can help, is for you to email me a copy of your file. I'll then make the required modifications and return the file. If you happen to have sensitive data, replace it with fictitious data that still reflects the type of data you're working with. I'll only require a small number of records.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca