Excel vba: Create a macro to connect a slicer to multiple pivot tables

Question

I am trying to create a macro that will change the data source for three pivot tables. Then, a single slicer that is already on the sheet will be connected to all three pivot tables. The pivot tables all will take their data from the same place.

When I try this, I get an error at the step to connect the slicer to pivot tables 2 and 3. If I run the macro with only the steps to change the data source for each pivot table, I noticed that the slicer only has one option in the "report connections" box instead of 3.

Here is what I have:

Sheets("Sales").Select
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table3", Version _
:=xlPivotTableVersion15)

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table3", Version _
:=xlPivotTableVersion15)

ActiveSheet.PivotTables("PivotTable3").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table3", Version _
:=xlPivotTableVersion15)


ActiveWorkbook.SlicerCaches("Slicer_Year").PivotTables.AddPivotTable ( _
ActiveSheet.PivotTables("PivotTable1"))
ActiveWorkbook.SlicerCaches("Slicer_Year").PivotTables.AddPivotTable ( _
ActiveSheet.PivotTables("PivotTable2"))
ActiveWorkbook.SlicerCaches("Slicer_Year").PivotTables.AddPivotTable ( _
ActiveSheet.PivotTables("PivotTable3"))

try reading about Slicers and SlicerCache here jkp-ads.com/Articles/slicers03.asp. How to do it manually myexcelonline.com/blog/… — Shai Rado

Krzysztof Paruzel Krzysztof Paruzel · Accepted Answer · 2017-11-14T09:19:58

I had the same problem and it was the last parenthesis which was causing the error. So you should replace this:

ActiveWorkbook.SlicerCaches("Slicer_Year").PivotTables.AddPivotTable ( _
ActiveSheet.PivotTables("PivotTable2"))

with this:

 ActiveWorkbook.SlicerCaches("Slicer_Year").PivotTables.AddPivotTable _
 ActiveSheet.PivotTables("PivotTable2")

Also you could write your code in this way:

Dim WB as Workbook
Dim WS as Worksheet

Set WB = ActiveWorkbook
Set WS = WB.Sheets("Sales")

WB.SlicerCaches("Slicer_Year").PivotTables.AddPivotTable _ 
WS.PivotTables("PivotTable2")

Excel vba: Create a macro to connect a slicer to multiple pivot tables

1 Answers