New Member
September 18, 2018
Hi Catalin,
Thank you so much for the code! I've just tested it, and it works fine in smaller sheets (5 pivot tables connected to 5 slicers).
However, I tested it on one of our standard sheets (25 slicers, 50 pivot tables) and the marco seems to run indefinitely. I will keep testing it and maybe it really just takes a while to finish running.
Again, appreciate all the help! This saves us a lot of effort.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi Jose,
It's not an endless loop, it's limited to the number of pivots from that sheet, but will loop though all slicercaches and its slicers from the entire workbook, there is no way that I know of to select only the slicers from a single sheet, a slicercache is stored at workbook level.
It should be a fast loop though, to read that collection, even if it's large. I suspect that you have lots of pivots and most probably lots of formulas in that workbook, when connecting a pivot the calculation might slow down your file, so you can try running the code with calculation set to manual.
Power Pivot
Power Query
Power BI
December 8, 2020
Hi Catalin,
When I try your code on my worksheet I get Run-time error '1004' Application-defined or object-defined error at this line:
aSlicer.SlicerCache.PivotTables.AddPivotTable (pt(i))
I'm using Excel 365 and the data source for all pivot tables on the page is a from a Table created from a Power Query.
I've added a few slicers to one of the tables and I'm trying to connect them to the other 47 tables on the page.
If I try to connect a second table to any of the slicers manually I don't see any slicers appearing in the PivotTable Analyze > Filter Connections box.
I also don't see any of the pivot tables showing in Report Connections... list by right clicking any of the slicers. Could I have messed something up with the slicer cache by running the code as far as that line but it halting there?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Power Pivot
Power Query
Power BI
December 8, 2020
Hi, I ran that code from Debra (interestingly I couldn't find it on her website for a full explanation in context). After I did that and reopened the file, Excel told me it had to repair " Errors were detected in file '."Repaired Records: External data range from /xl/queryTables/queryTable1.xml part (External data range)".
After that it now works perfectly. Thank you
1 Guest(s)