If you are using a filter on a 'regular' pivot table (one that isn't using source data from Power Pivot), you can click on 'Show Report Filter Pages' in the PivotTable Options on the Ribbon
and this will create a separate sheet for every item in the filter, showing the pivot table filtered for that item.
Pivot Tables created from data stored in the data model (PowerPivot) aren't able to do this, but I've written some VBA that makes it happen.
First, let's quickly look at the setup. I have some data stored in a table that shows sales over a year for some products
I insert a pivot table and check the box (in red) to add the data to the data model. My pivot table is inserted on a new sheet.
The pivot table is set up like this
Changing the Filter With VBA
On a 'regular' pivot table you could work your way through the categories one by one setting the PageField to change the filter as I did here Automating and Emailing Pivot Table Reports.
But pivot tables connected to a Power Pivot data source are actually using OLAP cubes and the programming is different, to say the least.
So to make the programming easier, let's create a slicer and then manipulate that slicer with VBA.
Inserting a slicer for the category is straight forward. Click into a value field in the pivot table, then on the Ribbon -> PivotTable Analyze -> Insert Slicer
I'm going to filter by Category so check that box and click on OK.
We get this slicer.
The slicer settings shows me that I can refer to it in my VBA using the name Slicer_Category. You'll need to change this to match your slicer.
This is the code to drive the slicer and create the sheets for each filter field.
The While loop works through each of the categories in the slicer.
SliceItem stores the name of the selected category which is subsequently used to rename the new sheet.
Changing VisibleSlicerItemsList changes the slicer.
The code then copies the current sheet (creating a new sheet), renames it to the category name, and then heads back to the pivot table sheet to continue.