locked
How to Filter pivot table based on cell values RRS feed

  • Question

  • Hi there!
    I need to filter a date field in pivot table in a dynamic way. From what I've searched, since I'm using an OLAP source I do not have many possibilities other than VBA.
    I've created a cell within the worksheet where I'll retrieve the date needed: =WORKDAY(TODAY();-2)
    Then I tried recording a Macro of copying that value and pasting in the filter but obviously the macro got a fixed date and not the actual action of me copying and pasting.

    Sub PTdatefilter()
    '
    ' PTdatefilter Macro
    '
    
    '
        Range("C4").Select
        Selection.Copy
        ActiveSheet.PivotTables("PivotTable2").PivotFields( _
            "[Table].[date].[date]").VisibleItemsList = Array( _
            "[Table].[date].&[2016-10-13T00:00:00]")
        Range("C5").Select
        Application.CutCopyMode = False
        Selection.Copy
        ActiveSheet.PivotTables("PivotTable2").PivotFields( _
            "[Table].[date].[date]").VisibleItemsList = Array( _
            "[Table].[date].&[2016-10-12T00:00:00]", _
            "[Table].[date].&[2016-10-13T00:00:00]")
    End Sub

    This is what I currently have. I needed the dates to either be based on cells I created to have the dates I need, or just have it directly in the code. I saw that there's a application.workday function but I don't have enough VBA knowledge to use it :| ( yet )
    Looking forward to your help!
    Many thanks


    • Edited by Nunoadlima Saturday, October 15, 2016 5:43 PM
    Saturday, October 15, 2016 5:41 PM

Answers

  • Assumed the date criteria is in C5 cell. See if below does the job. I could not OLAP based pivot table but tested ok in non-olap table.

    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
            "[Table].[date].[date]").pivotfilters.add type:=xlcaptionequals,value1:=range("c5").value
    


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    • Marked as answer by Nunoadlima Monday, October 17, 2016 5:33 PM
    Saturday, October 15, 2016 6:34 PM
    Answerer

All replies

  • Assumed the date criteria is in C5 cell. See if below does the job. I could not OLAP based pivot table but tested ok in non-olap table.

    ActiveSheet.PivotTables("PivotTable2").PivotFields( _
            "[Table].[date].[date]").pivotfilters.add type:=xlcaptionequals,value1:=range("c5").value
    


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    • Marked as answer by Nunoadlima Monday, October 17, 2016 5:33 PM
    Saturday, October 15, 2016 6:34 PM
    Answerer
  • Hi Asadulla!

    Many thanks for your help. That worked perfectly :)

    I had some issues testing it out because if I tried to run it while it had the filter already it would give me an error but fixed by clearing the label filter first.

    Also in one of the situations I needed to values, but since it was between two days just changed it to xlcaptionsIsBetween

    Thanks a lot!

    Monday, October 17, 2016 5:33 PM
  • Good to see it helped.

    "I had some issues testing it out because if I tried to run it while it had the filter already it would give me an error but fixed by clearing the label filter first."

    There are 4 method of PivotField by which you can clear filter. These are

    ClearAllFilters

    ClearLabelFilters

    ClearManualFilter

    ClearValueFilters

    Their effects are self-explanatory


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Monday, October 17, 2016 6:16 PM
    Answerer