Results 1 to 9 of 9

Thread: Excel 365 Advanced Filtering
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2005
    Location
    Tasmania
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel 365 Advanced Filtering

    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

  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,053
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 365 Advanced Filtering

    I have not looked in options. By macro:
    Code:
    Sub ADF()
        Application.Dialogs(xlDialogFilterAdvanced).Show xlFilterCopy
    End Sub

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel 365 Advanced Filtering

    @Kenneth
    It's not a standard option that can be changed.
    I think you solution is the best option
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular
    Join Date
    Nov 2005
    Location
    Tasmania
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 365 Advanced Filtering

    Kenneth and Michael

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

    Best regards

    Mike

  5. #5
    Board Regular
    Join Date
    Nov 2005
    Location
    Tasmania
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 365 Advanced Filtering

    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

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel 365 Advanced Filtering

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #7
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    3,053
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 365 Advanced Filtering

    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 by Kenneth Hobson; Sep 12th, 2019 at 07:48 PM.

  8. #8
    Board Regular
    Join Date
    Nov 2005
    Location
    Tasmania
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 365 Advanced Filtering

    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

  9. #9
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,806
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel 365 Advanced Filtering

    just wish my old brain were a few years younger
    You and me both Mike !!!!!
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •