I am using a raw data (source data) which fluctuates every week from 60,000 lines to 90,000 lines. I have created around 9 different pivot tables and 7 different slicers by using the same raw data. All the pivot tables have been connected to all the slicers.
Every time the raw data changes, I have to first go through and disconnect every single slicer, refresh the pivot tables, then reconnect every slicer. Not a viable solution for my clients.
I found below mentioned code to disconnect all slicers but that is only half the issue. So I'm not sure where to go from here. VBA code to change the data source seems to run into the same issue and it crashes when it runs into a pivot that is connected to multiple slicer.
Dim oSliceCache As SlicerCache
Dim PT As PivotTable
Dim i As Long
For Each oSliceCache In ThisWorkbook.SlicerCaches
For i = .Count To 1 Step -1
The last thought I had was maybe somehow looping through the slicers and storing their names and settings in some sort of array, disconnecting them, updating the data source and then reconnecting them, or similarly looping first through the Pivot Table, finding any slicers connected to it and then doing those same steps, but I have to admit I haven't used arrays extensively and never with slicers so I'm not sure if this is a good idea or if so how to even get started on it.
I am asking the Excel/VBA geniuses out there, to help me with a code which changes the data source and refresh the pivot table by disconnecting and reconnecting slicers automatically.
Appreciate any help or advice.
Looking forward for the positive response!