Log In
Register
Home
New Member
June 9, 2020
Offline
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?
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?
this is my formula:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),””)
October 5, 2010
Offline
Hi,
I took your source data table and copied it to another sheet called New Data. I removed blank columns. In the source data table I added a new column to replace the formul1 measure you created in Power Pivot.
I then created a PivotTable from this new data and loaded it into Power Pivot.
Filtering of this pivot table is much faster than with yours and the macro to create the report pages works as it should.
You can see all of this in the attached file.
Your PP model appears to be struggling with something. You should certainly remove blank data and you shouldn't be creating a measure that is generating errors. You are trying to add numbers and text.
Try tidying up the data and try again. Do you even need to use Power Pivot? You can create a regular pivot table and slicer and generate the report filter pages from PivotTable Analyze -> Options.
When I run the VBA against your pivot table it takes far too long to run - again I think a symptom of issues with the PP model and data.
I've moved the VBA into their own code modules rather than storing them in the sheet module. Every time the sheet was copied so was the VBA.
I made a copy of the code so that you can run it against my own Power Pivot Pivot Table. It's in Module 2 and it's called xPPReportFilterPages().
With regards to your formula to extract a unique list, I'm not quite sure what you are doing there. In Column AA there's a list of numbers but they are all the same. Maybe if you can provide a more detailed example and some example results I can help.
Regards
Phil
1 Guest(s)
