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.
You can run the macro by creating a shortcut or using an icon on your QAT.
Download the Workbook With Sample Code
Enter your email address below to download the workbook with the data and code from this post.
Dear Team
thanks for shairing your expertise, i would like to send sliced division power pivots sheet to division heads
but its pivot cache carries entire data set, please suggest while spilting how can we restrict cache set
for only relevant divion getting shared with each of dision sheet so they also can look into details of basic
data of pivot made
Hi Vipul,
If you’re using Power Pivot, then you can’t prevent the division heads from seeing the complete data set.
Mynda
The VBA code you provided in Show Report Filter Pages for Power Pivot PivotTables works brilliantly. Is there a way of tweaking the VBA so that the report pages also include the pivot table for all items in the slicer as well as the individual elements?
Hi Bob,
Not sure what you mean. All report pages already include all data, each page is just filtered for a specific category. If you clear the category filter on any report page, you will see all data.
Cheers,
Catalin
this is my formula:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),””)
Hi Miri,
Please start a topic on our forum and attach your workbook so I can take a look for you.
Regards
Phil
in one of my formula, i want to Dynamically extract a list of unique values from a column range with formula, so i press the Ctrl + Shift + Enter keys simultaneously. when i delete this formula, the result of macro is true. how can i use your macro while i have this formula in my sheet?
thank you very much. I used this macro, but the new created sheets are linked to each other and all of them show the same slicer item. even, when i change slicer item in one sheet, all other sheets change to that slicer item. i have formula out of pivot area which is based on pivot data, when i delete this formula, the result of macro is OK. what should i do? i need this formula be in the pivot table sheet. how can i send my file to see it?
Hello, I downloaded the file, saved it to my pc and tried to run the macro. I got a run-time error “Method ‘_Default’ of object ‘SlicerCaches’ failed. The Excel version is 1908 (Build 11929.20776). Is it due to the version build of excel or something else? I didn’t make any changes to the file, just saved it to a local (not onedrive) drive.
Hello again, Never mind. I realized I had another workbook open but not active and for some reason that caused the problem. When I closed excel and opened only your workbook it worked fine.
Glad you got it working, Kathleen.