Eliminate Pivot Table Annoyances
Results 1 to 7 of 7

Thread: Autofilter multiple sheets with manual date range

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Autofilter multiple sheets with manual date range

    I have a workbook that has multiple sheets from multiple linked datasources. I would like to have a Dashboard page that I am able to select a type a date range in B1 and C1 (start and end date) and it filter Column A in all of the other worksheets. I found the code below, but this looks at the Range(B1) and Range(C1) in their respective sheets not back to the "Dashboard" where the dates would have been set.

    Sub apply_autofilter_across_worksheets()
    'Updateby Extendoffice 20160623
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
    xWs.Range("A1").AutoFilter 1, Criteria1:=">=Range(B1)", Operator:=xlAnd, Criteria2:="<=Range(C1)"
    Next
    End Sub


    Thanks for time in advance

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,196
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Autofilter multiple sheets with manual date range

    If your dashboad is on Sheet1, eg. then
    Code:
    xWs.Range("A1").AutoFilter 1, Criteria1:=">=Sheets("Sheet1").Range(B1)", Operator:=xlAnd, _
    Criteria2:="<=Sheets("Sheet1").Range(C1)"
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Autofilter multiple sheets with manual date range

    Quote Originally Posted by JLGWhiz View Post
    If your dashboad is on Sheet1, eg. then
    Code:
    xWs.Range("A1").AutoFilter 1, Criteria1:=">=Sheets("Sheet1").Range(B1)", Operator:=xlAnd, _
    Criteria2:="<=Sheets("Sheet1").Range(C1)"

    Thanks for quick help.

    This code is giving a compile error "Expected: end of statement" after the first Sheets("Sheet1").

    I originally had that Sheet named "Dashboard" but was throwing a syntax error so I renamed it to Sheet 1 and getting that error above.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    10,305
    Post Thanks / Like
    Mentioned
    195 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Autofilter multiple sheets with manual date range

    Try
    Code:
    xWs.Range("A1").AutoFilter 6, Criteria1:=">=" & Sheets("Sheet1").Range("B1") & "", Operator:=xlAnd, _
    Criteria2:="<=" & Sheets("Sheet1").Range("C1") & ""
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  5. #5
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,196
    Post Thanks / Like
    Mentioned
    13 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Autofilter multiple sheets with manual date range

    Tx, @Fluff. good catch.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  6. #6
    New Member
    Join Date
    Dec 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Autofilter multiple sheets with manual date range

    Quote Originally Posted by Fluff View Post
    Try
    Code:
    xWs.Range("A1").AutoFilter 6, Criteria1:=">=" & Sheets("Sheet1").Range("B1") & "", Operator:=xlAnd, _
    Criteria2:="<=" & Sheets("Sheet1").Range("C1") & ""

    That did it!

    Thanks for the help, both of you!

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    10,305
    Post Thanks / Like
    Mentioned
    195 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Autofilter multiple sheets with manual date range

    Glad we could help & thanks for the feedback
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

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
  •  

 

DMCA.com