• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Show Report Filter Pages for Power Pivot PivotTables

You are here: Home / Excel VBA / Show Report Filter Pages for Power Pivot PivotTables
show report filter pages for power pivot pivottables
May 27, 2020 by Philip Treacy

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

Show Report Filters Ribbon Item

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

Sales data in Excel table

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.

create pivot table and add to data model

The pivot table is set up like this

Pivot table Fields

Pivot table using Power Pivot

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.

Insert slicer

We get this slicer.

Finished 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.

Slicer settings

This is the code to drive the slicer and create the sheets for each filter field.

VBA to control pivot table using slicer

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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook.

show report filter pages for power pivot pivottables

More Excel PivotTable Posts

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. I’m talking about PivotTable cells containing the (blank) placeholder.
Conditional Formatting PivotTables

Conditional Formatting PivotTables

Conditional Formatting PivotTables values areas will automatically expand/contract as you add new data or make changes to the filters, rows or columns.
Display Missing Dates in Excel PivotTables

Display Missing Dates in Excel PivotTables

Filter Excel Pivot Tables by Values

Filter Excel Pivot Tables by Values

Excel PivotTable Month Comparison [Video]

Excel PivotTable Month Comparison [Video]

Excel PivotTable Month Comparison is easy with the Show Values As tool.
Excel PivotTable Add Percentage of Total Column [Video]

Excel PivotTable Add Percentage of Total Column [Video]

Excel PivotTable Add Percentage of Total Column is easy via the Show Values As settings.
Excel Factor 6 Auto Refresh PivotTables

Excel Factor 6 Auto Refresh PivotTables

Excel Factor Entry 1 - Reverse PivotTable

Excel Factor Entry 1 – Reverse PivotTable

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Excel VBATag: Excel PivotTable
Previous Post:Gather Data with Excel FormsGather Data with Excel Forms
Next Post:Dynamic Data Validation Lists in Userformsdynamic data validation lists in userforms

Reader Interactions

Comments

  1. Bob Pannell

    July 15, 2020 at 9:04 pm

    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?

    Reply
    • Catalin Bombea

      July 16, 2020 at 12:53 pm

      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

      Reply
  2. miri

    June 9, 2020 at 7:51 pm

    this is my formula:
    =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),””)

    Reply
    • Philip Treacy

      June 9, 2020 at 7:58 pm

      Hi Miri,

      Please start a topic on our forum and attach your workbook so I can take a look for you.

      Regards

      Phil

      Reply
  3. miri

    June 9, 2020 at 7:49 pm

    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?

    Reply
  4. miri

    June 9, 2020 at 6:33 pm

    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?

    Reply
  5. Kathleen Heath

    May 29, 2020 at 12:25 am

    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.

    Reply
    • Kathleen Heath

      May 29, 2020 at 12:28 am

      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.

      Reply
      • Mynda Treacy

        May 29, 2020 at 8:46 am

        Glad you got it working, Kathleen.

        Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.