(Solved) – Using VBA to connect PivotTables with a Slicer

(solved)-–-using-vba-to-connect-pivottables-with-a-slicer

I’m working on a dashboard for work in excel, and I have it all working well except for one part. I refactored some of the pivot tables in order 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 thought it would be simple 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. The problem lies in the last stage; after successfully 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, and I tried recording a macro of me doing this but even the recorded macro doesn’t work?

Here is what I’m trying to do.

 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"))

AFAIK, this should work. It’s the exact macro recorded when I did it manually but it refuses to work, throwing the error:

Run-time error ‘1004’: Unable to get the PivotTables property of the
Worksheet class

My slicer is indeed named ‘Slicer_Shift’, and the table names are correct. I’m at a complete loss for what the issue is and would really appreciate any assistance or insight anyone can provide!

Using Excel 2010, btw.

Leave a Reply

Your email address will not be published. Required fields are marked *