Log In
Register
Home
February 11, 2021
Offline
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
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
Answers Post
February 11, 2021
Offline
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 🙂
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
February 11, 2021
Offline
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
This this line instead of the one highlighted in yellow from your image:
sl.PivotTables.AddPivotTable pvt
February 11, 2021
Offline
Thank u but it's Same thing, it says application-defined or object-define
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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/xl.....vot11.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.
1 Guest(s)
