VBA to Find Pivot Table Filters

[sirguise]

I'm stumped , I'm tired , I'm frustrated . Hopefully someone much smarter than I already has the answer.

Excel 2007, I'm trying to cheat into a "multi-box" by using a pivot table filter without a pivot table. I'd like to capture the selected values so I can pass into a SQL statement.

In my case the pivot table is "Division" and lets say the values are "Accounting", "Executive", "Sales", "Admin", "Development". If the user selects "Accounting" and "Executive", how can I grab those two values?

Thanks,

Dave

 

[AlphaFrog]

This loops through the items in pivot table "Division" in field "Dept" and shows a messagebox for each selected item in that field. I don't follow what you want to do other than that.

    Dim pi As PivotItem
    
    For Each pi In ActiveSheet.PivotTables("Division").PivotFields("Dept").PivotItems
        If pi.Visible Then MsgBox pi.Name & " is selected"
    Next pi

 

[sirguise]

First off, thank you very much, that will get me moving in the right direction.

What I'm trying to accomplish is grabbing the value(s) and place in variables which I use in a "Cube" formula.

But it was my understanding that in 2007 there was an easier method than running a loop.

 

[simchuck]

I think my issue is similar...

I want to extract the selected values from my pivot table filter so that I can display them on the page. I am using a data set with over 18000 records which includes 300+ "buildings". When multiple values are selected in the filter drop-down, the only way I can confirm which values were selected is to scroll through the drop-down and remember.

I tried =GETPIVOTDATA("Building Name",MyPivotTable), but wasn't surprised when this didn't work, even entering as an array formula.

Any ideas? Will the VBA code from AlphaFrog allow me to create a UDF to return these selected filter values? Is there something built-in that would be easier?

 

[sirguise]

Yes and No. What he provided was only how to find the items selected, in order to get them to display somewhere you then have to figure out how to manipulate his statement and for me that was putting it in a loop with a counter that I could point to a display section. This is just one of the routines I use, but should be pretty similar to what you require. If you take this code into a macro and point it at your pivot table/filter, you should just be able to add a button or something to your sheet to fire it off when you want to build your list. A little tip, add a clearcontents line to remove the old results before you create the new list.

Dim Company As PivotItem
Dim CompCount As Integer
Dim CompTotal As Integer
CompCount = 2 'This is your starting row for display
    For Each Company In ActiveSheet.PivotTables("ISTrendFilter").PivotFields("Company").PivotItems
 
        If Company.Visible Then
        CompCount = CompCount + 1
        CompTotal = CompTotal + 1
        Range("ISTrendFilters!A" & CompCount) = Company.Name '& CompCount
        End If
    Next Company
    Range("ISTrendFilters!A2") = MaxComp - CompTotal

In my scenario, the user can look at multiple company's in an income statement. When they pick the values from the pivot filter, they can then hit a refresh button which fires off this (and other) code. This then runs the loop against the company - pivot table filter and drops the values on a sheet called ISTrendFilters. The final line places the total count in A2 so I can compare selected vs total and determine if "select all" was used and therefore whether I actually need to factor in the filter at all.

Hope this helps! Dave.

 

[RoryA]

If you are selecting items in the dropdown, rather than using the new filters, you can use the VisibleItems collection:

Sub testing()
   Dim pt As PivotTable
   Dim varFilter
   Set pt = ActiveSheet.PivotTables(1)
   varFilter = GetItemList(pt.RowFields(1))
   MsgBox varFilter
End Sub
Function GetItemList(PF As PivotField) As String
   Dim pi                As PivotItem
   Dim strOut            As String
   For Each pi In PF.VisibleItems
      strOut = strOut & "," & pi.Caption
   Next pi
   GetItemList = Mid$(strOut, 2)
End Function

for example. This function returns a comma-separated string so if you need an array you can use Split on the output.

 

[simchuck]

That helps, but still not exactly what I need. I think I may be using some incorrect terminology, so I will try to clarify exactly what I am doing.

My database includes information on monthly account billings for a number of different buildings. My pivot table is designed to let me quickly review for a given building (or group of buildings) the account billing values for each billing period. So the pivot table looks like so...

Building (Multiple Items)

Sum of Values
Month | Acct1 Acct2 Acct3
------+--------------------------
Jan {
Feb { monthly account values here
Mar {
...


So when I select multiple buildings (is that properly referred to as a 'page'? -- in the Excel 2010 PivotTable Field List it shows up under the title 'Report Filter'), the table displays "(Multiple Items)". The actual buildings that I have selected from the 'page' is the information that I need. Preferably want to return this as an array, but I can work with the parsed string method.

I don't know enough about PivotTables or VBA to fill in all of the details for the end product, but I think the essence is there in the previous responses.

 

[simchuck]

Thanks AlphaFrog, sirguise, and rorya.

I am finally getting my head wrapped around this (had to put it aside to concentrate on other pressing matters), and came up with the following code based on your inputs, which provides the basic functionality needed:

Function PTfilters(PT As PivotTable) As String

Dim Filter As PivotItem
Dim NoFilters As Integer

   NoFilters = 0
   
   For Each Filter In PT.PivotFields("MyPivotFilters").PivotItems
      If Filter.Visible Then
         NoFilters = NoFilters + 1
         PTfilters = PTfilters & "," & Filter.Name
      End If
   Next Filter

   PTfilters = Mid$(PTfilters, 2)
   
End Function

There are a couple more things which I would like to do with this that would allow me to use the function more generally:

1. Generalize to allow specification of the "PivotFields" through a function argument, or perhaps even better, automatically identify the pivot table filters. In my case (only one parameter in the filter list) it seems that the pivot table filters can be specified using PivotFields(2). Why does this work, and when might it not work?

2. Return the filter list as an array, rather than as a CSV string.

I am relatively inexperienced with this level of VBA, so hopefully someone can help me find solutions. Also, I noticed there is an ActiveFilters property -- could these be used instead of the loop to return the active/visible filters in the pivot table?

Thanks in advance for any additional help!

 

[MYKRPL]

Hi , I am in a similar situation. I want to display selected values from a Pivot filter into another cell. I am trying to use the code below but it is good to display data for first row of pivot. When I try to change the Row Field to PivotFields it display all values of filter not selected one. I also need to refresh cell value display selected when selection is changed.

Please help me in this I am very new of VB.

If you are selecting items in the dropdown, rather than using the new filters, you can use the VisibleItems collection:

Sub testing()
   Dim pt As PivotTable
   Dim varFilter
   Set pt = ActiveSheet.PivotTables(1)
   varFilter = GetItemList(pt.RowFields(1))
   MsgBox varFilter
End Sub
Function GetItemList(PF As PivotField) As String
   Dim pi                As PivotItem
   Dim strOut            As String
   For Each pi In PF.VisibleItems
      strOut = strOut & "," & pi.Caption
   Next pi
   GetItemList = Mid$(strOut, 2)
End Function

for example. This function returns a comma-separated string so if you need an array you can use Split on the output.