Creating Pivot Table in Macro with Filter Value

Question

I am working on a macro to run automatically that will create new tables with report filter criteria. I have 5 filter criteria So Cal, Chicago, Georgia, Ohio and Texas. The way I have it setup right now is to copy the pivot table and just put a new filter value. In the code below I just change the "Chicago" to "Texas" for the next pivot table. The issue is that not all of the filter criteria will always be in the data. The way it is setup is that it will create a pivot table with all the data from the data table. There are 2 options I either don't want it to create a pivot table if the value is not in the filter criteria or just leave the pivot table blank.

Sheets("Sheet3").Name = "So Cal Summary"
Range("B35").Select
Sheets("So Cal Summary").Select
Sheets("So Cal Summary").Copy Before:=Sheets(2)
Sheets("So Cal Summary (2)").Select
Sheets("So Cal Summary (2)").Name = "Chicago Summary"
ActiveSheet.PivotTables("PivotTable5").PivotFields("Pool").CurrentPage = _
    "CHICAGO"

This may not be the correct way to code. I used the macro recording in order to get this code. I don't have a coding background but can do enough with recording macros and then manipulating.

JasonAizkalns JasonAizkalns · Accepted Answer · 2014-11-24T20:59:32

Have you tried using Show Report Filter Pages... ?

This is available under PivotTable Tools > Options > Options (Dropdown) > Show Report Filter Pages...

This will generate a new tab for each "Page" in the "Filter". Another option would be to use Slicers.

Creating Pivot Table in Macro with Filter Value

1 Answers