none
How can I access selected report filter values in Excel Pivot table from VBA code or Formulas? RRS feed

  • Question

  • I have a pivot table with report filter field. I need to do some operations based on the value user selects in report filter field.

    If user selects single value (or selects All) - no problem to get that value. In sample video it's cell B1, but in case of multiple values are selected, this field shows just Multiple selected.

    Where can I get the specific reporting filter values that are selected? Can I access those values from Excel formulas? If not - can I access those values from VBA code?

    Sample video

    Thursday, April 9, 2015 12:54 PM

Answers

  • Sub test()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
        Set pt = ActiveSheet.PivotTables(1)
        Set pf = pt.PivotFields("myField")
        For Each pi In pf.PivotItems
            Debug.Print pi.Name, pi.Visible
        Next
    End Sub

    • Marked as answer by Prokurors Friday, April 10, 2015 6:17 AM
    Thursday, April 9, 2015 1:48 PM
    Moderator
  • This solution looks interesting and it did work the first time - after I added the code from the solution - saved and reopened the file, but... no reaction afterwards - no matter what I select in the report filter it's not displayed :(

    Any ideas what should I check?

    Here's a sample workbook...

    https://onedrive.live.com/redir?resid=41260C1C818928B2!646&authkey=!ACQqDE3IqTdalK0&ithint=file%2cxlsm


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Prokurors Friday, April 10, 2015 6:29 AM
    Thursday, April 9, 2015 7:54 PM
  • (this is a reply to Peter Thornton's post)

    Tried this with some newly created pivot table and it works.

    But using it on my legacy pivot table (i think it was created with Excel 97 or something) shows some interesting results, maybe You can help me understand/solve them.

    Datasource has 88 different values for filter field (I can see exactly those when I manually click on dropdown menu to select specific values), but with Your code i get >300 pivot items for this field... investigating their names I see that those most likely are values that have been used in the past, but they are NOT in the Data source range any more.

    Data source for this pivot table is 'Raw Data'!$A$1:$N$64798

    I decreased it just by one row and as a result now it shows those 88 values not >300, so THANK YOU, the problem is solved, but I still wanted to understand WHAT and WHY was happening:

    so I thought maybe file has somehow cashed those old table values. Then I changed Data source to the original value and thought that now it should work, but no - now again it shows >300 items in report filter field.

    Then I selected each of A64798 to N64798 cell and there is no value in there.

    Do You have any clue what could be happening here?


    Try setting the MissingItemsLimit to None before refreshing the cache...

    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Prokurors Friday, April 10, 2015 6:21 AM
    Thursday, April 9, 2015 7:57 PM

All replies

  • Sub test()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
        Set pt = ActiveSheet.PivotTables(1)
        Set pf = pt.PivotFields("myField")
        For Each pi In pf.PivotItems
            Debug.Print pi.Name, pi.Visible
        Next
    End Sub

    • Marked as answer by Prokurors Friday, April 10, 2015 6:17 AM
    Thursday, April 9, 2015 1:48 PM
    Moderator
  • See if the following solution helps...

    http://www.mrexcel.com/forum/excel-questions/643523-display-values-selected-inside-pivot-filter.html


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Thursday, April 9, 2015 1:53 PM
  • (this is a reply to Peter Thornton's post)

    Tried this with some newly created pivot table and it works.

    But using it on my legacy pivot table (i think it was created with Excel 97 or something) shows some interesting results, maybe You can help me understand/solve them.

    Datasource has 88 different values for filter field (I can see exactly those when I manually click on dropdown menu to select specific values), but with Your code i get >300 pivot items for this field... investigating their names I see that those most likely are values that have been used in the past, but they are NOT in the Data source range any more.

    Data source for this pivot table is 'Raw Data'!$A$1:$N$64798

    I decreased it just by one row and as a result now it shows those 88 values not >300, so THANK YOU, the problem is solved, but I still wanted to understand WHAT and WHY was happening:

    so I thought maybe file has somehow cashed those old table values. Then I changed Data source to the original value and thought that now it should work, but no - now again it shows >300 items in report filter field.

    Then I selected each of A64798 to N64798 cell and there is no value in there.

    Do You have any clue what could be happening here?


    • Edited by Prokurors Thursday, April 9, 2015 3:30 PM to clarify to whom I am responding
    Thursday, April 9, 2015 3:29 PM
  • This solution looks interesting and it did work the first time - after I added the code from the solution - saved and reopened the file, but... no reaction afterwards - no matter what I select in the report filter it's not displayed :(

    Any ideas what should I check?

    Thursday, April 9, 2015 3:56 PM
  • I have no idea what was going on there with your 300/88 items, how curious! Did you try refreshing, manually or with code pt.PivotCache.Refresh
    Thursday, April 9, 2015 4:16 PM
    Moderator
  • just tried with pt.PivotCache.Refresh but it does not give any result
    Thursday, April 9, 2015 5:25 PM
  • I'm out of ideas, curious though! Anyway, it's all sorted now, right?
    Thursday, April 9, 2015 7:16 PM
    Moderator
  • This solution looks interesting and it did work the first time - after I added the code from the solution - saved and reopened the file, but... no reaction afterwards - no matter what I select in the report filter it's not displayed :(

    Any ideas what should I check?

    Here's a sample workbook...

    https://onedrive.live.com/redir?resid=41260C1C818928B2!646&authkey=!ACQqDE3IqTdalK0&ithint=file%2cxlsm


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Prokurors Friday, April 10, 2015 6:29 AM
    Thursday, April 9, 2015 7:54 PM
  • (this is a reply to Peter Thornton's post)

    Tried this with some newly created pivot table and it works.

    But using it on my legacy pivot table (i think it was created with Excel 97 or something) shows some interesting results, maybe You can help me understand/solve them.

    Datasource has 88 different values for filter field (I can see exactly those when I manually click on dropdown menu to select specific values), but with Your code i get >300 pivot items for this field... investigating their names I see that those most likely are values that have been used in the past, but they are NOT in the Data source range any more.

    Data source for this pivot table is 'Raw Data'!$A$1:$N$64798

    I decreased it just by one row and as a result now it shows those 88 values not >300, so THANK YOU, the problem is solved, but I still wanted to understand WHAT and WHY was happening:

    so I thought maybe file has somehow cashed those old table values. Then I changed Data source to the original value and thought that now it should work, but no - now again it shows >300 items in report filter field.

    Then I selected each of A64798 to N64798 cell and there is no value in there.

    Do You have any clue what could be happening here?


    Try setting the MissingItemsLimit to None before refreshing the cache...

    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Prokurors Friday, April 10, 2015 6:21 AM
    Thursday, April 9, 2015 7:57 PM
  • Thanks, Domenic!

    xlMissingItemsNone did the trick

    Friday, April 10, 2015 6:22 AM
  • Thanks, now it works fine! This was helpful! :)
    Friday, April 10, 2015 6:29 AM
  • Hi Prokurors,

    I am facing the same issue can you please share the sample excel which worked for reference!

    Thanks,


    Vijaivel


    • Edited by Vijaivel Wednesday, October 28, 2015 12:35 PM
    Wednesday, October 28, 2015 12:17 PM
  • Sorry Vijaivel, I missed your post...

    but just in case you have not resolved your issue - Domenic Tamburino (a few post higher) has provided an Excel sample workbook - that should be what you need.

    Friday, October 28, 2016 8:42 AM