Hello,
I can't seem to find any working code for syncing two or more slicers.
I have two pivot tables now with similar data, and when I choose one slicer I want the other to be connected to the first one.
I found a code but it doesn't seem to be working on my file.
Here is the code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sub ConnectAllPivotsToAllSlicers()
Dim TargetPTDict As Variant, TargetSheet As Worksheet, PvTable As PivotTable
Set TargetPTDict = CreateObject("Scripting.Dictionary")
Set TargetSheet = ThisWorkbook.Worksheets("Sheet1")
Dim slCache As SlicerCache, slCachePivotTable As PivotTable, SlItem As Variant, pt As Variant, i As Byte, aSlicer As Slicer
'get the list of pivot tables from that sheet
For Each PvTable In TargetSheet.PivotTables
TargetPTDict.Add Key:=PvTable.Parent.Name & PvTable.Name, Item:=PvTable
Next
'loop through all slicers from all slicercaches
For Each slCache In ThisWorkbook.SlicerCaches
For Each aSlicer In slCache.Slicers
If aSlicer.Parent.Name = TargetSheet.Name Then 'this slicer is in the target sheet
For Each SlItem In TargetPTDict.Keys
'connect all pivots to this slicer
pt = TargetPTDict.Items
If UBound(pt) >= LBound(pt) Then
For i = LBound(pt) To UBound(pt)
aSlicer.SlicerCache.PivotTables.AddPivotTable (pt(i))
Next
End If
Next
End If
Next
Next
Set TargetPTDict = Nothing
End Sub
Hi Riste,
You can't sync these slicers because the pivot tables they are attached to have different data sources and therefore different pivot caches. What do you want the 1st slicer to show when you have Plane selected in the 2nd slicer?
You can use Power Pivot to do something like this but you'd need to explain what it is you are trying to do in order for us to give you an answer.
Regards
Phil
Hi Philip,
The different data source is the point that I want to sync both tables.
What I want is: when I select i.e.Car and Bike from the first slicer, the second one to show also Car and Bike from the second pivot table.
Also, I found this code from Mr.Excel, but it doesn't seem to work either:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim sc1 As SlicerCache Dim sc2 As SlicerCache Dim SI1 As SlicerItem Dim sc3 As SlicerCache Dim sc4 As SlicerCache Dim SI3 As SlicerItem ' These names come from Slicer Settings dialog box Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Name") Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Name1") Set sc3 = ThisWorkbook.SlicerCaches("Slicer_Region2") Set sc4 = ThisWorkbook.SlicerCaches("Slicer_Region1") Application.ScreenUpdating = False Application.EnableEvents = False sc2.ClearManualFilter sc4.ClearManualFilter For Each SI1 In sc1.SlicerItems sc2.SlicerItems(SI1.Name).Selected = SI1.Selected Next SI1 For Each SI3 In sc3.SlicerItems sc4.SlicerItems(SI3.Name).Selected = SI3.Selected Next SI3 Application.EnableEvents = True Application.ScreenUpdating = True End SubThank you. Regards, Riste.
Hi Riste,
Why not rearrange your source data into a single table. Then create 2 pivot tables from the same source, that way you can connect the slicer for each PT together and they each filter the other. See attached.
Regards
Phil
Hello Philip,
Thank you for your effort but the point is to connect the two tables.
If it was one, I know how to do it and it is easy(without VBA).
This way, with the code it should work the connection between the two tables, caches etc but I can't find out why it doesn't.
If you could find out or somebody else knows, please help me.
Thank you.
Best Regards,
Riste.
