Excel 365 Advanced Filtering

[mikemcbain]

I use this on a number of large files (in 2003 compatibility mode because it is much faster) and when I click Data - Advanced it always comes up with the "Filter the list in place" and every time I must click "Copy to another location" before OK.

Is there any way to make Copy to another location the default?

With great hope of a solution

Mike

 

[Kenneth Hobson]

I have not looked in options. By macro:

Sub ADF()
    Application.Dialogs(xlDialogFilterAdvanced).Show xlFilterCopy
End Sub

 

[Michael M]

@Kenneth
It's not a standard option that can be changed.
I think you solution is the best option

 

[mikemcbain]

Kenneth and Michael

Thank you both for your esponses and I will use that macro code

Best regards

Mike

 

[mikemcbain]

Michael M

The code that Kenneth supplied me - see below stalls at the Advanced filter window with the Copy to another location ticked as requested but then I must click OK for it to do the extraction? How do I get it to run the extraction after that box is ticked?
Simple I'm sure but I am such a dunce with VB.

Mike.
Code:
Sub ADF()
Application.Dialogs(xlDialogFilterAdvanced).Show xlFilterCopy
End Sub

 

[Michael M]

Maybe this might be a better option for you....from ExtendOffice
In each case you simply select the range when the box pops up
you can modify to suit

Sub Advancedfiltertoanothersheet()
'provided by Extendoffice
    Dim xStr As String, xAddress As String, xRg As Range
    Dim xCRg As Range, xSRg As Range
    On Error Resume Next
    xAddress = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select the filter range:", "Filter for Excel", xAddress, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xCRg = Application.InputBox("Please select the criteria range:", "Filter for Excel", "", , , , , 8)
    If xCRg Is Nothing Then Exit Sub
    Set xSRg = Application.InputBox("Please select the output range:", "Filter for Excel", "", , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xRg.AdvancedFilter xlFilterCopy, xCRg, xSRg, False
    xSRg.Worksheet.Activate
    xSRg.Worksheet.Columns.AutoFit
End Sub

 

[Kenneth Hobson]

The goal for #1 was to check the CopyTo box. The method that I showed does that. I guess any dialog is going to '"stall". That is its purpose.

IF your new goal in #5 is to not do anything other than run the macro, then a one line macro would suffice, if the copyto range input was on the activesheet. Of course the three input ranges would need to be used in that one line.

If you are wanting to let the user pick the three inputs, then I don't know how one click to run the filter for #2 is that difficult. Method #6 is 3 OK button clicks to do the final run.

If you wanted to pre-fill the inputs but allow changes using method #2 , here is an example.

Application.Dialogs(xlDialogFilterAdvanced).Show xlFilterCopy, ActiveSheet.UsedRange, "Sheet2!E1:F2", "H1:M1"

 

[mikemcbain]

Kenneth & Michael

Your detailed responses are absolutely brilliant and I appreciate your time immensely.

Thank you both and just wish my old brain were a few years younger to have more time in the future to benefit even more!

Best regards

Mike
Tasmania

 

[Michael M]

just wish my old brain were a few years younger

You and me both Mike !!!!!