I am trying to create a macro (Excel 2016) to connect all slicers from all pivot tables from different sheets similar the below disconnect code but for connect but idk how 🙁 help plz
Sub DisconnectAllSlicers()
Dim SlicersDict As Variant
Set SlicersDict = CreateObject("Scripting.Dictionary")
Dim sl As SlicerCache, slpt As PivotTable, SlItem As Variant
'create a dictionary with slicers and connected pivot tables
For Each sl In ThisWorkbook.SlicerCaches
SlicersDict.Add Key:=sl.Name, Item:=sl.PivotTables
Next
'take each slicer
For Each SlItem In SlicersDict.Keys
'remove pt connections for this slicer
For Each slpt In SlicersDict(SlItem) 'for each pivot table controlled by this slicer,
slpt.SaveData = True
ThisWorkbook.SlicerCaches(SlItem).PivotTables.RemovePivotTable (slpt)
Next slpt
Next SlItem
Set SlicersDict = Nothing
End Sub
Do the opposite:
aSlicer.SlicerCache.PivotTables.AddPivotTable (pt(i)) (instead of removepivottable)
Code should look like below, but be aware that all pivot tables must have the SAME pivot cache, otherwise code will fail:
For each wks in thisworkbook.worksheets
for each pvt in wks.pivottables
sl.SlicerCache.PivotTables.AddPivotTable (pvt)
next pvt
next wks
Next sl
Thank You Mr.Bombea . can you do me a favor, could you please write full code to connect all slicers , im so poor at VBA codes and English also 🙂
Basically, you had most of it.
Sub DisconnectAllSlicers()
Dim sl As SlicerCache, pvt As PivotTable, wks as worksheet
For Each sl In ThisWorkbook.SlicerCaches
For each wks in thisworkbook.worksheets
for each pvt in wks.pivottables
sl.SlicerCache.PivotTables.AddPivotTable (pvt)
next pvt
next wks
Next sl
End Sub
Unfortunately it's not working as attached, it says object doesn't support this property or method 🙁 is there another way to do it
This this line instead of the one highlighted in yellow from your image:
sl.PivotTables.AddPivotTable pvt
Thank u but it's Same thing, it says application-defined or object-define
d error as attached 🙁
I tested that change on my side and works.
I believe the code fails now for the reason mentioned above:
that all pivot tables must have the SAME pivot cache, otherwise code will fail. If 2 pivot tables have different pivot caches, you CANNOT connect them to the same slicer.
You might have pivot tables from different sources or multiple pivot caches.
You can find here codes to deal with multiple pivot caches:
https://www.contextures.com/xlpivot11.html
You can disable errors, if you are sure that there are different data sources that cannot have the same cache.
in this case, just put
On Error Resume Next
above the line highlighted.
If the tables have the same source, you have to run code to change the pivot cache for all pivots.
It's works finally , thank you for helping me and god blessing you 🙂
