Excel 365 Advanced Filtering

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
152
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I have not looked in options. By macro:
Code:
Sub ADF()
    Application.Dialogs(xlDialogFilterAdvanced).Show xlFilterCopy
End Sub
 
Upvote 0
@Kenneth
It's not a standard option that can be changed.
I think you solution is the best option
 
Upvote 0
Kenneth and Michael

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

Best regards

Mike
 
Upvote 0
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
 
Upvote 0
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
Code:
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
 
Upvote 0
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.
Code:
Application.Dialogs(xlDialogFilterAdvanced).Show xlFilterCopy, ActiveSheet.UsedRange, "Sheet2!E1:F2", "H1:M1"
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top