Changing Pivot Table Report Filter via VBA with a Named Range or Array

[analyst44]

Hi there,

I've spent hours trying to figure this out and have gotten so many whacky results with various things I've tried over the last few days that I'm at wit's end. I've consulted Contextures, Mr. Excel and probably 3-4 other sites for previous posts on the topic and many seem to have ideas for what I'm trying to do but I can't get it to work in practice.

To summarize the problem: I have a set of four pivot tables on a sheet that I need to programmatically change a Report Filter (Page Field) so I can create sets of reports in an automated fashion. This will be the first step in that process. The change will involve choosing > 1 Role each time the code loops through based on Named Ranges I've defined that are associated with that Role.

My code thus far:

Sub TestCode()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Dim RolePick As Range
Dim ReportPick As Range

Set RolePick = [emm_dc_gsr]

For Each pt In Sheets("Master_Pivot").PivotTables
    pt.ManualUpdate = True
    
    Set pf = pt.PivotFields("Role")
    On Error Resume Next
    With pf
        .AutoSort xlManual, .SourceName, .EnableMultiplePageItems
    
        For Each pi In pf.PivotItems
             pi.Visible = False
'       Next pi
        
        For Each pi In pf.PivotItems
            If pi.Value = RolePick Then
                pi.Visible = True
            Else: pi.Value = False
            End If
        Next pi
       
    End With

Next pt

pt.ManualUpdate = False

End Sub

emm_dc_gsr is one of many Named Ranges that will contain a variable number of elements. Just using the one right now to see if I can get the code to work, I'll eventually make another Named Range/Array of all them so I can loop through each Report ("ReportPick").

I want the Report Filter to consult that Named Range for its values and apply those values to PivotField "Role" that is used as a Report Filter.

When running this code above, I get a "Role" Field that says "All" but no values (the table is completely blank), with no evidence as to why it'd be blank (all filters in every Report, Column and Row are working normally and are filled in). When I choose a value manually after the code is run, the pivot table values populate. Do I need to somehow index the Named Range in that loop? I'm just confused about this step right here:

For Each pi In pf.PivotItems
If pi.Value = RolePick Then
pi.Visible = True
Else: pi.Value = False


When I've run other versions of the code, I've gotten an array version of it to "work" using LBound and UBound, but it never chooses the right two values even though those are verified as stored in the array via a pass-through. It chooses the first few values in the Report Filter.

Here's the corresponding code for that:

For i = LBound(myArray) To UBound(myArray)
pf.PivotItems(i).Name = myArray(i, 1).Value
pf.PivotItems(i).Visible = True
Next


I do not care if I use an array or a Named Range. I just want something that is simple and works. Passing the values directly from the named range seems easiest to my brain, but I'm open to anything and I'm clearly missing something (probably silly).

I also have no idea why " .AutoSort xlManual, .SourceName, .EnableMultiplePageItems" is necessary though every piece of sample code I've seen seems to have some variation of it.


(Using Excel 2010, Windows 7.)

Thanks so much for your time!!

 

[Jerry Sullivan]

Here's a function that can your Sub can call to filter the PivotTables...

The second parameter can be either an array or a range.

Private Function Filter_PivotField(pvtField As PivotField, _
        varItemList As Variant)
    Dim strItem1 As String, blTmp As Boolean, i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    With pvtField
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        For i = LBound(varItemList) To UBound(varItemList)
            blTmp = Not (IsError(.PivotItems(varItemList(i)).Visible))
            If blTmp Then
                strItem1 = .PivotItems(varItemList(i))
                Exit For
            End If
        Next i
        If strItem1 = "" Then
            MsgBox "None of filter list items found."
            Exit Function
        End If 
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 And _
                  .PivotItems(i).Visible = True Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
    End With
End Function

Your main code might look something like the example below.
(I haven't tested this, so you might have to correct the reference to the RolePick range).

Sub TestCode()
    Dim pt As PivotTable
    Dim RolePick As Range
    Set RolePick = [emm_dc_gsr]
 
    For Each pt In Sheets("Master_Pivot").PivotTables
        Filter_PivotField _
            pvtField:=pt.PivotFields("Role"), _
                varItemList:=Application.Transpose(RolePick)
    Next pt
End Sub

 

[analyst44]

Thanks so much for taking the time to respond. I'm actually on holiday for the weekend, but I'll test yours as soon as I get back to see if I can make it work. Much appreciated, JS!

I guess I was also wondering where I was making the mistake in my own logic with the loop through the array/named range. Did you see anything that stuck out? I'm really trying to learn the syntax for what I was working on and I'm just a bit confused where it went wrong and why.

 

[Jerry Sullivan]

I guess I was also wondering where I was making the mistake in my own logic with the loop through the array/named range. Did you see anything that stuck out? I'm really trying to learn the syntax for what I was working on and I'm just a bit confused where it went wrong and why.

Here's a few spots where your code had errors or might not be doing what you intended.

The On Error Resume Next statement probably made it harder for you to catch these errors, since it allows the Sub to continue after errors.
It's probably a good idea to get your code to work without On Error Resume Next first (with scenarios that won't cause errors), then add that in to handle exceptions.

#1 When filtering the Report Filter (Page Field) to show more than one item, you will need to set .EnableMultiplePageItems = True for your code to work as intended. However, the statement below will cause an error.

With pf
    .AutoSort xlManual, .SourceName, .EnableMultiplePageItems

You need to set each property separately and also include the new value:

    .SourceName = True
    .EnableMultiplePageItems = True
    '   .AutoSort= xlManual '??? This isn't an allowed value

#2 Unfortunately, Excel won't let you set all PivotItems to .Visible=False ...even if you are going to set some of them to True in the next step. So this won't work...

For Each pi In pf.PivotItems
    pi.Visible = False
Next pi

In the code example I posted, the first step is to find at least one PivotItem that could be set to Visible=True.


#3 Looks like the intent here was to say "if the current pi.Value is found within the range RolePick, then make it visible."

For Each pi In pf.PivotItems
    If pi.Value = RolePick Then
        pi.Visible = True
    Else: pi.Value = False
    End If
Next pi

This isnt' correct syntax though since If pi.Value=RolePick will not search the whole range for a match.


#4 Your idea of stepping through each item in your Array of items to be shown is better than stepping through each PivotItem to see if it is in your list.

For i = LBound(myArray) To UBound(myArray)
   pf.PivotItems(i).Name = myArray(i, 1).Value
   pf.PivotItems(i).Visible = True
Next

However, the problem with this code as written is that it will try to set the name of each PivotItem to the name of the item in the array.
This won't work unless your array list matches the items and order of the pivot item lists. If that isn't the case, it will "remap" some of your PivotItems labels so that they don't match the data they are summing.


Hope this helps!

 

[analyst44]

Thanks for taking the time to go through that and give me the tips/advice.

I'm going to comb over this in even more detail tomorrow and try to get my version of the code working per the original post you made.

Thank you very much for your help! :D

 

[analyst44]

Here's a function that can your Sub can call to filter the PivotTables...


Your main code might look something like the example below.
(I haven't tested this, so you might have to correct the reference to the RolePick range).

Sub TestCode()
    Dim pt As PivotTable
    Dim RolePick As Range
    Set RolePick = [emm_dc_gsr]
 
    For Each pt In Sheets("Master_Pivot").PivotTables
        Filter_PivotField _
            pvtField:=pt.PivotFields("Role"), _
                varItemList:=Application.Transpose(RolePick)
    Next pt
End Sub

The second parameter can be either an array or a range.

    For Each pt In Sheets("Master_Pivot").PivotTables
        Filter_PivotField _
            pvtField:=pt.PivotFields("Role"), _
                varItemList:=Application.Transpose(RolePick)
    Next pt
 
 
Private Function Filter_PivotField(pvtField As PivotField, _
        varItemList As Variant)
    Dim strItem1 As String, blTmp As Boolean, i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    With pvtField
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        For i = LBound(varItemList) To UBound(varItemList)
            blTmp = Not (IsError(.PivotItems(varItemList(i)).Visible))
            If blTmp Then
                strItem1 = .PivotItems(varItemList(i))
                Exit For
            End If
        Next i
        If strItem1 = "" Then
            MsgBox "None of filter list items found."
            Exit Function
        End If 
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 And _
                  .PivotItems(i).Visible = True Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
    End With
End Function

Hi JS411. I'm finally able to dedicate some time to this. The code works splendidly, now I just need to further my understanding! Thank you for all of your help Can I step-through the code with you to make sure I understand it?

Here's what I've got so far:

So, we've got a separate function that can be called relatively easily with a few variables defined. The top portion basically says:

For every pivot table on the Sheet "Master_Pivot" call the function but first set the PivotField in question to "Role" and set the varItemList to the transposed values of whatever is in the variable RolePick, which is a Named Range with multiple values. I'm assuming that the transpose step makes it easier to cycle through values or does it have something to do with the fact that it's a variant/array? I wasn't sure about that.

We step into the "Role" PivotField and start performing some operations. I didn't understand the .Orientation syntax. I'm assuming that means, if "Role" is located in the Report Filter section, then allow multiple selections for it? Does orientation refer to the location of the PivotField in the table (i.e., Rows, Columns, Page/Report Filter)?

For every item in the Named Range (Array?) contained within varItemList, return FALSE if setting that property .Visible is an error and return TRUE if it's not an error. I'm kinda confused here. Is that right? I didn't get why you did this. I have a feeling you're trying to hollow out the Roles so that there are no values checked immediately and then to load the array with the values that will ultimately be checked, but I'm not quite following the method to get there.

If it's not an error, load the varItemList array's current position (whatever "i" is) with the value of strItem1. Then you exit the for function if one value has been found without looping?

If strItem1 is blank, show a message box and exit.

Take the value of the Role contained in strItem1 and make the PivotItem visible. Is this to make sure that at least one item is visible at all times to avoid a problem?

---------

For every value in Role's PivotItems that's both not strItem1 AND that's not already visible, set that visbile value to FALSE. Loop

For every value in the Named Range of varItemList, set all PivotItems visibility to TRUE. Loop.


I think I can reason that everything above the dashed line is somehow necessary, but I'm just feeling bad that I can't decipher it!

Thank you again for all of your help!

 

[Jerry Sullivan]

Glad the code worked and it's great to see your interest in VBA.

Here are my thoughts on your questions...

For every pivot table on the Sheet "Master_Pivot" call the function but first set the PivotField in question to "Role" and set the varItemList to the transposed values of whatever is in the variable RolePick, which is a Named Range with multiple values. I'm assuming that the transpose step makes it easier to cycle through values or does it have something to do with the fact that it's a variant/array?

Transpose is an easy way to load the values in a range into the Variant Array. If the range is 1 column by 10 rows Transpose loads that into a Variant that is 1 row by 10 columns.

We step into the "Role" PivotField and start performing some operations. I didn't understand the .Orientation syntax. I'm assuming that means, if "Role" is located in the Report Filter section, then allow multiple selections for it? Does orientation refer to the location of the PivotField in the table (i.e., Rows, Columns, Page/Report Filter)?

Yes, this refers to which section of the Pivot Table layout the field being filtered is located. If it is in the Page/Report Filter section, the code needs to do some things differently than if the field was in the Rows or Columns section.

For every item in the Named Range (Array?) contained within varItemList, return FALSE if setting that property .Visible is an error and return TRUE if it's not an error. I'm kinda confused here. Is that right? I didn't get why you did this. I have a feeling you're trying to hollow out the Roles so that there are no values checked immediately and then to load the array with the values that will ultimately be checked, but I'm not quite following the method to get there.

This intent of this step is just to set one of the PivotItems to Visible=True. We could just take the first item in the Named Range/Array and set it to Visible; however if it doesn't exist in the PivotTable Field, then we would be stuck. So this step just finds one item in the Named Range/Array that actually exists, and sets it to Visible.

If it's not an error, load the varItemList array's current position (whatever "i" is) with the value of strItem1. Then you exit the for function if one value has been found without looping?

Yes, once we have found and fulfilled the requirement to always have one pivot item be Visible, we can go to the next step of setting all the others to Visible=False.

Take the value of the Role contained in strItem1 and make the PivotItem visible. Is this to make sure that at least one item is visible at all times to avoid a problem?

Yes.

Hope this helps!

 

[analyst44]

Thank you so much, Jerry. I learned a lot from this thread. I very much appreciate you taking the time to go step-by-step with me.

I think I'm going to take the Excel Hero Academy soon to deep dive into the VBA aspects at a deeper level and try to build from the ground up. I'm excited to keep learning for sure!

 

[analyst44]

Hi there. Another problem stemming from this. Man, there have been many this week! But, I'm learnin'. I'm learnin'!

I did not account for the possibility that a named range with just *one* value could exist which would blow up any code like:

For i = LBound(varItemList) To UBound(varItemList)

...due to a type mistmatch.

So, I'm trying to do error handling that would look at varItemList and say, "Do you have more than one item or just one?" I'm doing this by testing if the value is an Array. If it's not an Array, treat it as a String and proceed with separate code. This seems to work ok so far, but I've had a few hiccups that I'm still working through that I'm puzzled about, but more on that after this gets resolved.

Even if it does work, I also feel that my code is not good given I'm not really accounting for it and adjusting for the change as much as I'm now working with two code sets -- one for the single-element case and another for the multiple case (which has been working just fine as provided by you, thank you!). This seems woefully ineffiecient to me.

Another reason I feel this code is not great is because I also don't know if the type Variant is seen, with certainty, as an Array when it has multiple values and, with certainty, as a String when it only has one (my values will always be text based, though). From what I've read, if a Variant Type has one value, it will take the Variable Type of that value?

My hunch is that I'd rather somehow look at the dimensions of the Variant and if it's (1) or (1,1) (not sure how it treats a single value when defined as Variant), I'd somehow account for that. If its > 1, I'd run your code as executed.

Any suggestions?

Thank you so much!

Private Function Filter_PivotField(pvtField As PivotField, _
        varItemList As Variant)
 
    Dim strItem1 As String, blTmp As Boolean, i As Long
'    On Error Resume Next
    Application.ScreenUpdating = False
    With pvtField
 
     If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        [COLOR=red]If Not IsArray(varItemList) Then[/COLOR]
[COLOR=red]       strItem1 = .PivotItems(varItemList)[/COLOR]
[COLOR=red]       GoTo SelectRole[/COLOR]
[COLOR=red]   End If[/COLOR]
 
        For i = LBound(varItemList) To UBound(varItemList)
            blTmp = Not (IsError(.PivotItems(varItemList(i)).Visible))
            If blTmp Then
                strItem1 = .PivotItems(varItemList(i))
                Exit For
            End If
        Next i
        If strItem1 = "" Then
            MsgBox pt.Name & vbNewLine & rReportNames.Value & vbNewLine & varItemList
            'MsgBox "None of filter list items found."
            Exit Function
        End If
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 And _
                  .PivotItems(i).Visible = True Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
[COLOR=red]SelectRole:[/COLOR]
[COLOR=red]   .PivotItems(strItem1).Visible = True[/COLOR]
[COLOR=red]   For i = 1 To .PivotItems.Count[/COLOR]
[COLOR=red]       If .PivotItems(i) <> strItem1 And _[/COLOR]
[COLOR=red]             .PivotItems(i).Visible = True Then[/COLOR]
[COLOR=red]           .PivotItems(i).Visible = False[/COLOR]
[COLOR=red]        End If[/COLOR]
[COLOR=red]   Next i[/COLOR]
 
    End With
 
End Function

 

[Jerry Sullivan]

Another problem stemming from this. I did not account for the possibility that a named range with just *one* value could exist which would blow up any code like:

For i = LBound(varItemList) To UBound(varItemList)

...due to a type mistmatch.

Hello again!

You've taught me something there - I didn't know that that when loading range value(s) into a variant, it doesn't become an array data type if there is only one element.

You're right that it's undesirable to have two separate blocks of code for the one cell and more than one cell condition.

A few ways to approach this are:

1. Test the count of cells in the range of value(s) to display prior to calling the Filter_PivotField function, If there is only one item, then either:

a. Set that one value to visible using a simpler function or inline code or

b. Wrap that string value in an Array and use that as your second parameter for the Filter_PivotField

Sub TestCode_1a()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("PivotTable1")
    If [emm_dc_gsr].Cells.Count = 1 Then
        Filter_PivotField_Single_Value pvtField:=pt.PivotFields("Role"), _
            varItem:=[emm_dc_gsr].Value
    Else
        Filter_PivotField pvtField:=pt.PivotFields("Role"), _
            varItemList:=Application.Transpose([emm_dc_gsr])
    End If
End Sub
 
Sub TestCode_1b()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("PivotTable1")
    If [emm_dc_gsr].Cells.Count = 1 Then
        Filter_PivotField pvtField:=pt.PivotFields("Role"), _
            varItemList:=Array([emm_dc_gsr].Value)
    Else
        Filter_PivotField pvtField:=pt.PivotFields("Role"), _
            varItemList:=Application.Transpose([emm_dc_gsr])
    End If
End Sub

2. Test the Variable Type of the parameter inside the Filter_PivotField function and wrap the value in an Array if it is not already an Array.

Modify the original code like...

    Application.ScreenUpdating = False
 
    If Not (IsArray(varItemList)) Then
         varItemList = Array(varItemList)
    End If
 
    With pvtField

In concept Option 1a is slightly more efficient (speed of execution); but for that miniscule difference, option 2 is a better choice for simplicity and maintenance of the code.

The addition of this check inside the function makes the code more robust too.

Thanks for helping improve this to be a better general-purpose function.