A key function for me when using Pivots is using the "show report filters page" function so that I can create a sheet per customer without having to do any manual filtering and saving of files.
I am now building pivots with measures and now when I try use that function it is greyed out. I know that sometimes this can be an issue as to what order excel looks at the data to perform functions, but I have gone into the main options and the "Prefer the Excel Data Model..." is disabled, so this isnt causing the function to not be available.
Can you advise as to how I enable this. If this is not available with measures or power pivot, do you have suggested alternatives as this is a key time saver and a function I cant live without.
Thanks so much!
Hi Declan,
Unfortunately, 'Show report filter pages' is not available with Power Pivot PivotTables. The only workaround would be to write a VBA routine that automatically copies the sheets and changes the filter.
Myndaa
Hi Declan,
If you can supply your workbook I'll try writing the necessary VBA. It shouldn't take too long as it'll be a modification of the code for this
https://www.myonlinetraininghub.com/automating-emailing-pivot-table-reports
regards
Phil
Hi Phil
Its still a bit work in progress and due to confidentiality I cant share the actual client file.
But if I sent you an example file could you see if it works? and is the code generic enough that I could then just apply this to my client copy, or is there a lot of customisation in it that has been managed?
Thanks
yes plz. If you can create a PT with the same structure, but without the private data, I think that'll be enough.
Hi Phil
Many thanks, I will create the file and send to you. What do you meant by create a PT?
Hi Declan,
Create a PT = Create a PivotTable.
If your dummy workbook can have a PivotTable with the same fields/structure-visualize-data as the real thing, then when I write the code it will match your actual data and require less modification to get the code to work with your real PT.
Regards
Phil
Hi Declan,
I've written some VBA that will do this for you
Show Report Filter Pages for Power Pivot PivotTables
Regards
Phil
