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!!

 

[analyst44]

No... thank YOU, Jerry!

I'm going to test both of these tomorrow. I agree with you about Option 2. It just seems easier to me.

I can't wait to start the Excel Hero Academy course in a few days. I'm tired of cobbling together my VBA stuff and would really like to have a great foundational understanding of how the engine works in terms of classes, objects and all the rest. I think it'll open up a world of possibilities -- and, maybe I can help some people one day, too!

 

[eyong123]

Hi I have a problem in pivot table and I wish to filter by using an input box where the user put the week numbers for which they want to filter and then on validate the input is used as filter criteria.

thanks in advance

 

[analyst44]

Hi I have a problem in pivot table and I wish to filter by using an input box where the user put the week numbers for which they want to filter and then on validate the input is used as filter criteria.

thanks in advance

Without having tried it, one easy method I'd suggest is that you supply two input boxes -- one for start week number and one for end week number. Pass those directly to an array within VBA using code or to cells that are pre-marked as a named range like s_date and e_date. I used the latter method, though I gave them drop-downs in cells via Data Validation before the code is run rather than use input boxes.

Either should work pretty seamlessly, though.

Hope that helps!

 

[DMG001]

Jerry,

I have been attempting to link my pivot table filter to a named range for a few days and have only managed to get 90% close!, just come across your previous post from a few years ago, quick copy and paste of your code and nailed it in minutes!! massive thanks for posting all those years ago!

 

[Jerry Sullivan]

Jerry,

I have been attempting to link my pivot table filter to a named range for a few days and have only managed to get 90% close!, just come across your previous post from a few years ago, quick copy and paste of your code and nailed it in minutes!! massive thanks for posting all those years ago!

It's nice of you to provide the feedback and I'm happy you found this old thread useful.