Help with VBA - PivotTable Macro

[astrontelstar]

My company provides me a PivotTable report in Excel. I need the report filtered by a series of ZipCodes. I would like assistance with something easier than manually going through the filter and selecting 200+ zip codes.

When using a macro recorder to see what Excel would "auto generate" for me, I got the following code about 50 times, as their are thousands of zip codes in this column.

I am looking to see if someone can help me with a simple macro loop that would take a set of zip codes like "75010, 75020, 75030" maybe in a string and change the pivotTable to filter by those, without the thousands of lines the Macro recorder seems to one to do.

Anyone? My boss just handed me the list of zip codes so I have them as outlined above.

Help?

With ActiveSheet.PivotTables("PivotTable3").PivotFields("shipping_zip")
.PivotItems("00000").Visible = False
.PivotItems("00802").Visible = False
.PivotItems("00918").Visible = False
.PivotItems("01001").Visible = False
.PivotItems("01002").Visible = False
.PivotItems("01007").Visible = False
.PivotItems("01013").Visible = False
.PivotItems("01020").Visible = False
.PivotItems("01027").Visible = False
.PivotItems("01028").Visible = False
.PivotItems("01033").Visible = False
.PivotItems("01034").Visible = False
.PivotItems("01038").Visible = False
.PivotItems("01040").Visible = False
.PivotItems("01050").Visible = False
.PivotItems("01053").Visible = False

 

[Domenic]

Assuming that "Sheet1" contains your pivot table, and that the pivot table is named "PivotTable1", try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] FilterZipCodes()

    [COLOR=darkblue]Dim[/COLOR] oPivotItem [COLOR=darkblue]As[/COLOR] PivotItem
    [COLOR=darkblue]Dim[/COLOR] vZipCodes [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] vMatchVal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    
    vZipCodes = Array("75010", "75020", "75030") [COLOR=#008000]'change and/or add zip codes as desired[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("shipping_zip")
        .ClearAllFilters
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oPivotItem [COLOR=darkblue]In[/COLOR] .PivotItems
            vMatchVal = Application.Match(oPivotItem.Value, vZipCodes, 0)
            oPivotItem.Visible = [COLOR=darkblue]Not[/COLOR] IsError(vMatchVal)
        [COLOR=darkblue]Next[/COLOR] oPivotItem
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Change the name of the sheet and pivot table, accordingly.

Hope this helps!