Excel 365 Advanced Filtering

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
76
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

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
I have not looked in options. By macro:
Code:
Sub ADF()
    Application.Dialogs(xlDialogFilterAdvanced).Show xlFilterCopy
End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,201
Office Version
2013
Platform
Windows
@Kenneth
It's not a standard option that can be changed.
I think you solution is the best option
 

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
76
Kenneth and Michael

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

Best regards

Mike
 

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
76
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,201
Office Version
2013
Platform
Windows
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
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,092
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:

mikemcbain

Board Regular
Joined
Nov 14, 2005
Messages
76
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
 

Forum statistics

Threads
1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top