My excel file has a sheet with 5 "global" slicers that I want to apply to all the pivot tables in the other sheets
Each other sheet has its own "local" slicers which are connected to all of its pivot tables
The pivots are all based on the same Power Pivot data model from an external SQL database.
When I copy a sheet to make a new one, all the "local" slicers are copied and automatically connected to the new sheet's pivot tables, however the connection to the global slicers is not maintained.
To solve this I made a VBA sub to reconnect all global slicers to each pivot table when a sheet is created, the problem is that each time a pivot is added to a slicerCache, it will refresh. That means n slicers x m pivot tables, which is a lot of refreshes on a huge dataset, so copying a new sheet can take half an hour.
I tried several methods in VBA, such as setting ManualUpdate to True on the pivotTables before each connection, which did not produce results, setting pivotCache.EnableRefresh to False, which technically works (no refresh )but does not allow the slicerCache connection to finalize so it reverts back.
So I feel like I've tried it all to no avail.
The only way that works with a single refresh per pivot is manually going to the relations menu and checking the boxes for all the global slicers, which connects all slicers to a pivot at once so it only refreshes once. The drawback to this manual method is that it takes me a lot of time to manually link all the pivots.
Is there something I've missing to better do this?
Attempt 1 - Programmatically get slicers and loop over them
For Each slicerCache In slicersOnSheet
pivotTable.ManualUpdate = True
slicerCache.PivotTables.AddPivotTable pivotTable
Next slicerCache
Attempt 2 - Manually list out slicers to avoid the for loop as I've read that "ManualUpdate = True" resets to False after every next instruction
pivotTable.ManualUpdate = True
slicerCache1.PivotTables.AddPivotTable pivotTable
pivotTable.ManualUpdate = True
slicerCache2.PivotTables.AddPivotTable pivotTable
...