Filtering to show two specific choices in a pivot table report filter

Question

This feels like a dumb question, but will all of my Googling, I can't figure it out. I am writing a few VBA scripts to aid in quickly filtering some pivot tables.

Let's say I have a pivot table containing some standard sales data. One of the "Report Filter" fields is "State".

I have figured out how to show a single state:

Dim pt As PivotTable
Set pt = Sheets("Sales").PivotTables("SalesPivot")
pt.ClearAllFilters
pt.PivotFields("State").CurrentPage = "Oregon"

The above works well. But how would I do the exact same thing except showing "Oregon" and "California"? (Also, if there is a better way to do the above, I'm all ears)

By hand, I would first check the "select multiple items" box, then uncheck the "(All)" option to select none, then scroll through the options to find and check "California" and "Oregon", and then click OK.

Thanks!

Use code such as the question's code in stackoverflow.com/questions/14387938/… — MP24

Tom M. Tom M. · Accepted Answer · 2014-06-06T19:02:54

For future discoverers of this question, this is what I did:

Sub SelectMultiplePivotFilter()

Dim pt As PivotTable
Dim pf As PivotField
Set pt = Sheets("Sales").PivotTables("SalesPivot")
Set pf = pt.PivotFields("State")

pt.ClearAllFilters

For Each pi In pf.PivotItems
    If pi.Value = "California" Or pi.Value = "Oregon" Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next pi

End Sub

Filtering to show two specific choices in a pivot table report filter

1 Answers