Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Filter Pivot Table based on cell value

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Filter Pivot Table based on cell value

    folks, I have picked up extendoffice's worksheet change event which filters a pivot table from the value entered into a particular cell.
    https://www.extendoffice.com/documen...ell-value.html

    it works really well. I am using a date range for my filter. how do I get the filter to accept a greater than date?
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,880
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Filter Pivot Table based on cell value

    The date has a different control than a text.
    The following is assuming that the pivot table dates are as dd-mmm, example 30-sep. And in cell H6 you are capturing a date like 30-09-2019 or 30/09/2019

    Try :

    Code:
     Private Sub Worksheet_Change(ByVal Target As Range)'Update by Extendoffice 20180702
        Dim xPTable As PivotTable
        Dim xPFile As PivotField, xItem As PivotItem
        Dim xStr As String, n As Variant
        On Error Resume Next
        If Intersect(Target, Range("H6:H7")) Is Nothing Then Exit Sub
        Application.ScreenUpdating = False
        Set xPTable = Worksheets("Sheet1").PivotTables("PivotTable2")
        Set xPFile = xPTable.PivotFields("Date")
        xStr = CDate(Target.Text)
        xPFile.ClearAllFilters
        For Each xItem In xPFile.PivotItems
          'xPFile.CurrentPage = xStr
          n = CDate(xItem.Value & "-" & Year(Date))
          If n < xStr Then
            xItem.Visible = False
          End If
        Next
        Application.ScreenUpdating = True
    End Sub
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Pivot Table based on cell value

    hi dante, thanks for looking at this for me. I have adjusted your code for my sheet name, pivot field, and single cell reference as below.

    [code]Private Sub Worksheet_Change(ByVal Target As Range) 'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField, xItem As PivotItem
    Dim xStr As String, n As Variant
    On Error Resume Next
    If Intersect(Target, Range("H6")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Top 10").PivotTables("PivotTable1")
    Set xPFile = xPTable.PivotFields("Order Date")
    xStr = CDate(Target.Text)
    xPFile.ClearAllFilters
    For Each xItem In xPFile.PivotItems
    'xPFile.CurrentPage = xStr
    n = CDate(xItem.Value & "-" & Year(Date))
    If n < xStr Then
    xItem.Visible = False
    End If
    Next
    Application.ScreenUpdating = True
    End Sub[code/]

    the code fires and picks up the correct date from h6 which is held by xStr. However, after that, no changes are made to the pivot.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,880
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Filter Pivot Table based on cell value

    You could give an example of how your dates are in the cell and the pivot table.
    Regards Dante Amor

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Pivot Table based on cell value

    Quote Originally Posted by ajm View Post
    hi dante, thanks for looking at this for me. I have adjusted your code for my sheet name, pivot field, and single cell reference as below.

    [code]Private Sub Worksheet_Change(ByVal Target As Range) 'Update by Extendoffice 20180702
    Dim xPTable As PivotTable
    Dim xPFile As PivotField, xItem As PivotItem
    Dim xStr As String, n As Variant
    On Error Resume Next
    If Intersect(Target, Range("H6")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Set xPTable = Worksheets("Top 10").PivotTables("PivotTable1")
    Set xPFile = xPTable.PivotFields("Order Date")
    xStr = CDate(Target.Text)
    xPFile.ClearAllFilters
    For Each xItem In xPFile.PivotItems
    'xPFile.CurrentPage = xStr
    n = CDate(xItem.Value & "-" & Year(Date))
    If n < xStr Then
    xItem.Visible = False
    End If
    Next
    Application.ScreenUpdating = True
    End Sub[code/]

    the code fires and picks up the correct date from h6 which is held by xStr. However, after that, no changes are made to the pivot.
    I'm a dope. my worksheet name was "Top10" - no space. I have amended this and now the code runs. It doesn't pick up the correct dates though. If i use 18/09/2018 or 18-Sep-18 as my date in H6, the filter returns only 15 April 2019. The dates in Order Date are formatted dd-Mmm-yy. would this matter?
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,880
    Post Thanks / Like
    Mentioned
    83 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Filter Pivot Table based on cell value

    In my case, the dates on the pivot table appear like this (dd-mmm), Do not see the year, so the macro completes it. So, in your pivot table, how do the dates look?



    Regards Dante Amor

  7. #7
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Pivot Table based on cell value

    raw data
    Lease Type Repair Agreement Type Order Date
    Services Only Recharge Maintenance 17-May-18
    Services Only Recharge Maintenance 17-May-18
    Services Only Recharge Maintenance 17-May-18
    Services Only Recharge Maintenance 6-Feb-19
    Services Only Recharge Maintenance 6-Feb-19
    Services Only Recharge Maintenance 6-Feb-19
    Services Only Recharge Maintenance 6-Feb-19
    Services Only Recharge Maintenance 6-Feb-19
    Services Only Recharge Maintenance 6-Feb-19
    Services Only Recharge Maintenance 6-Feb-19
    Services Only Recharge Maintenance 6-Feb-19
    Services Only Recharge Maintenance 4-Apr-19
    Services Only Recharge Maintenance 4-Apr-19
    Services Only Recharge Maintenance 4-Apr-19
    Services Only Recharge Maintenance 1-Nov-18


    pivot dates

    can't seem to get the image for pivot dates to upload. they come through in the same format above dd-Mmm-YY
    Last edited by ajm; Sep 30th, 2019 at 09:25 PM.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  8. #8
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Pivot Table based on cell value

    so, by changing the one line in the code from:

    n = CDate(xItem.Value & "-" & Year(Date))

    to

    n = CDate(xItem.Value)

    the macro runs through and compares the target date to each item in the Order Date filter box, but still does not filter the pivot table
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  9. #9
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Pivot Table based on cell value

    further to the above, I reformatted the raw data Order Date column to dd/mm/yyyy and created a new pivot table on a new sheet. so now all dates are in the same format. using 18/9/2017 as my target date in H6, the resulting filter after the event code has run is completely random. this is so frustrating.
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

  10. #10
    Board Regular
    Join Date
    Feb 2003
    Location
    Brisbane, AUSTRALIA (GMT +10)
    Posts
    1,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter Pivot Table based on cell value

    I have found a query on Chandoo's site (https://chandoo.org/forum/threads/up...nge-vba.30954/) that I have put to use. this I what i have

    Code:
    Sub DateFilter()
    
    'Set the Variables to be used
    Dim pt As PivotTable
    Dim SDate As Date
    SDate = Worksheets("Top 10").Range("b1")
    'Amend here to filter your data
    Set pt = Worksheets("Top 10").PivotTables("PivotTable1")
        pt.PivotFields("Order Date").ClearAllFilters 'This line is needed to clear existing filter before applying new one
        pt.PivotFields("Order Date").PivotFilters.Add Type:=xlAfter, Value1:=CLng(SDate)
            
    End Sub
    ______________________________________________

    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Use code tags for posting VBA e.g.;
    [CODE ]your VBA code here[ /CODE]
    (with no spaces within the square [] brackets)

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
  •