I'm working on a dashboard in Excel.
I refactored some pivot tables to allow a single slicer to filter all the graphs, but then discovered that you can't simply change the data source of a pivot table that shares slicers with other tables.
I have a macro which refreshes a table of data and then modifies the data source of my tables to be the new range.
I want to disconnect the slicers from the tables, update the table, update the data source for the tables, and then reconnect the slicers to the tables.
In the last stage; after removing the slicers and updating the ranges in VBA, I find myself unable to reattach the slicers to the tables. I am able to do it manually by right clicking on the slicer -> 'PivotTable Connections' and checking all the boxes.
I recorded a macro of this.
ActiveWorkbook.SlicerCaches("Slicer_Shift").PivotTables.AddPivotTable ( _
ActiveSheet.PivotTables("dryer_table"))
ActiveWorkbook.SlicerCaches("Slicer_Shift").PivotTables.AddPivotTable ( _
ActiveSheet.PivotTables("operating_table"))
ActiveWorkbook.SlicerCaches("Slicer_Shift").PivotTables.AddPivotTable ( _
ActiveSheet.PivotTables("shift_table"))
It is Throwing the error:
Run-time error '1004': Unable to get the PivotTables property of the
Worksheet class
My slicer is named 'Slicer_Shift', and the table names are correct.
Using Excel 2010.
