Slicer Sync

Question

I have two source tables and several dozen based on them.

There is a common field for two tables with a common set of possible values.

I have two slicers (one per source table). Each slicer manages several related pivot tables.

I would like to be able to sync them.

That is, if the user selects the value A in Slicer_1, Slicer_2 is automatically updated to select the value A.

So I'm still very simple

ActiveWorkbook.SlicerCaches("Slicer_1").SlicerItems("A").Selected = ActiveWorkbook.SlicerCaches("Slicer_2").SlicerItems("A").Selected ActiveWorkbook.SlicerCaches("Slicer_1").SlicerItems("B").Selected = ActiveWorkbook.SlicerCaches("Slicer_2").SlicerItems("B").Selected ActiveWorkbook.SlicerCaches("Slicer_1").SlicerItems("C").Selected = ActiveWorkbook.SlicerCaches("Slicer_2").SlicerItems("C").Selected

Now, how would I get an automatic trigger when slicer_1 changes? I assigned the slicer_2 macro, but the update does not happen until the slicer button is pressed.

And how can I postpone execution until all changes are applied. At this time, it updates the field A (selected yes / no) updates my tables and goes to B, etc.

I want it to wait for an update until all slicer fields have been updated.

thanks

Answer 1

Slicer synchronization can be done in a general way.
With "generic", I mean that there should be no dependency on the (literal) slicer cache names, and synchronization can start from any slicer cache.

The approach to bring this to an end is to preserve the state of all slicer caching objects. After modifying the pivot table (underlying one or more slicer caches), the new states can be compared with the old states and recognized updated caches. From there, synchronization can be performed.

My solution consists of 4 steps:
1) create clsWrapperCache , a wrapper class around an Excel SlicerCache object
2) create clsWrapperCaches , a collection class of clsWrapperCache objects
3) create clsCacheManager , a manager class for working with the statuses of SlicerCache objects
4) ThisWorkbook by setting up calls to the manager

1) clsWrapperCache, a wrapper class around an Excel SlicerCache object

 ' wrapper class around Excel SlicerCache object Option Explicit Public Object As SlicerCache Public OldState As String Public Function CurrentState() As String ' state is set by: ' a) name of first visible slicer item ' b) number of visible slicer items Dim s As String If Object.VisibleSlicerItems.Count > 0 Then s = Object.VisibleSlicerItems.Item(1).Name Else s = "" End If s = s & vbCrLf ' separator that cannot be found in a SlicerItem name s = s & CStr(Object.VisibleSlicerItems.Count) CurrentState = s End Function 

clsWrapperCache contains an Excel SlicerCache object.
More importantly: it can manage the state of SlicerCache. Getting the state can be done very quickly, i.e. By concatenating:

• name of the first VisibleSlicerItem and
• number of elements VisibleSlicerItems.

OldState initially set in the Set_Caches routine (step 3) and can be reset in the de Synchronize_Caches routine (step 3) if the slicer cache is involved in the synchronization process.

2) clsWrapperCaches, object collection class clsWrapperCache

 ' clsWrapperCaches, collection class of clsWrapperCache objects Option Explicit Private mcol As New Collection Public Sub Add(oWC As clsWrapperCache) mcol.Add oWC, oWC.Object.Name End Sub Public Property Get Item(vIndex As Variant) As clsWrapperCache ' vIndex may be of type integer or string Set Item = mcol(vIndex) End Property Public Property Get Count() As Integer Count = mcol.Count End Property 

This is a simple collection class that simply contains clsWrapperCache objects. It will be used to store objects in the AllCaches collection.

3) clsCacheManager, a class for working with the statuses of SlicerCache objects

 Option Explicit Public AllCaches As New clsWrapperCaches Public Sub Set_Caches() Dim sc As SlicerCache Dim oWC As clsWrapperCache Dim i As Integer If Me.AllCaches.Count <> ThisWorkbook.SlicerCaches.Count Then ' a) on Workbook_Open event ' b) maybe the user has added/deleted a Slice Cache shape by hand Set AllCaches = New clsWrapperCaches For Each sc In ThisWorkbook.SlicerCaches 'create a wrapper SlicerCache object Set oWC = New clsWrapperCache Set oWC.Object = sc 'save current state of SlicerCache into OldState oWC.OldState = oWC.CurrentState ' add wrapper object to collection AllCaches.Add oWC Next End If End Sub Sub Synchronize_Caches() ' copy current selections from slicer caches "FromCaches" into any other slicer cache with same SourceName On Error GoTo ErrEx Dim oWCfrom As clsWrapperCache Dim oWCto As clsWrapperCache Dim scFrom As SlicerCache Dim scTo As SlicerCache Dim si As SlicerItem Dim i As Integer Dim j As Integer Application.EnableEvents = False ' prevent executing Workbook_SheetPivotTableUpdate event procedure Application.ScreenUpdating = False For i = 1 To Me.AllCaches.Count Set oWCfrom = Me.AllCaches.Item(i) If oWCfrom.CurrentState <> oWCfrom.OldState Then Set scFrom = oWCfrom.Object For j = 1 To Me.AllCaches.Count Set oWCto = Me.AllCaches.Item(j) Set scTo = oWCto.Object ' Debug.Print oWCto.Name If scTo.Name <> scFrom.Name And scTo.SourceName = scFrom.SourceName Then scTo.ClearAllFilters ' triggers a Workbook_SheetPivotTableUpdate event On Error Resume Next For Each si In scFrom.SlicerItems scTo.SlicerItems(si.Name).Selected = si.Selected Next On Error GoTo 0 ' update old state of wrapper object oWCto oWCto.OldState = oWCto.CurrentState End If Next ' update old state of wrapper object oWCfrom oWCfrom.OldState = oWCfrom.CurrentState End If Next Ex: Application.EnableEvents = True Application.ScreenUpdating = True Exit Sub ErrEx: MsgBox Err.Description Resume Ex End Sub 

The clsCacheManager class manages cache states using the Set_Caches and Synchronize_Caches methods.
Set_Caches : if the number of caches in this book is different from AllCaches, the AllCaches (re) collection is built. Thus, the OldState each slicer cache is preserved.

Synchronize_Caches : here all caches are passed. If the slicer cache is updated ( oWCfrom.CurrentState <> oWCfrom.OldState ), any other cache with the same SourceName will also be updated (for example, "year"). Updating is done by copying all selected slicer elements from the source cache to the destination cache. OldState for all involved caches reset for the current state at the end of the synchronization process.

4) ThisWorkbook by setting calls to the cache manager

 Option Explicit Private mCacheManager As New clsCacheManager Private Sub Workbook_Open() SetCacheManager mCacheManager.Set_Caches End Sub Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable) SetCacheManager mCacheManager.Set_Caches mCacheManager.Synchronize_Caches End Sub Private Sub SetCacheManager() If mCacheManager Is Nothing Then Set mCacheManager = New clsCacheManager End If End Sub 

In step 4, you can take advantage of 1 to 3: we can make CacheManager calls, such as SetCaches or Synchronize_Caches . This code is easy to read.

The advantages of this solution:

• works for all slicer caches in a workbook.
• independent of SlicerCache names
• very fast, because the state of the objects in the cache cache is very fast
• expandable. The clsCacheManager class can be extended to work with dependencies between slicer caches.

Answer 2

In the past, I ran into the same problem, and in my opinion, Pivot Table synchronization is easier than Slicers. When you connect several PivotTables (with the same cache) to Slicer, changing any of these Pivot Tables fields (from which you created Slicer) changes the selection of Slicer, as well as the rest of Pivot Tables.

So, for example, you have 12 pivot tables and 2 slicers, 6 are assigned 1 and 6 are assigned to another.
Also, let's say you have a common WorkWeek field with the exact same elements present in all pivot tables, you can try something like this:

 Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) On Error GoTo halt Application.EnableEvents = False Application.ScreenUpdating = False Dim ww As String, pF1 As PivotField, pF2 As PivotField Set pF1 = Me.PivotTables("PT1").PivotFields("WorkWeek") Set pF2 = Me.PivotTables("PT2").PivotFields("WorkWeek") Select Case True Case Target.Name = "PT1" ww = pF1.CurrentPage If pF2.CurrentPage <> ww Then pF2.CurrentPage = ww Case Target.Name = "PT2" ww = pF2.CurrentPage If pF1.CurrentPage <> ww Then pF1.CurrentPage = ww End Select forward: Application.EnableEvents = True Application.ScreenUpdating = True Exit Sub halt: MsgBox Err.Number & ": " & Err.Description Resume forward End Sub 

You put this code on a sheet that contains your target pivot tables (PT1 and PT2 in the example above).
Note the following assumptions for this example:

• PT1 and PT2 have a WorkWeek field in the report filter (not rows / columns).
• PT1 is connected to Slicer1, and PT2 is connected to Slicer2.
• Multiple selections are not allowed (at least for the above setting).

So basically when you change the PT1 WorkWeek selection that is associated with Slicer1, and the PT2 changes also change, which in turn also changes the selection of Slicer2.
If you change the selection to Slicer1 or 2, the same effect will occur.
Any change in selection in Slicer1 will take effect on Slicer2.
This is just an idea. I do not know if you put fields in the report filter or rows / columns.
You can customize the specified pattern to suit your needs just in case. To select multiple items, you will need to use a loop to assign and select each item. NTN.

Answer 3

I ended up using this code:

 Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim sc1 As SlicerCache Dim sc2 As SlicerCache Dim si1 As SlicerItem Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Cache1") Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Cache2") Application.ScreenUpdating = False Application.EnableEvents = False sc2.ClearManualFilter For Each si1 In sc1.SlicerItems sc2.SlicerItems(si1.Name).Selected = si1.Selected Next si1 MsgBox "Update Complete" clean_up: Application.EnableEvents = True Application.ScreenUpdating = True Exit Sub err_handle: MsgBox Err.Description Resume clean_up End Sub 

code>

It is linked as an update trigger to one of my pivot tables.

Answer 4

I used the code below. It also adds the names selected on the slicer to the names of the Header fields that I mention in the headers of the pivot tables.

 Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Dim pi As PivotItem Dim dest As PivotField If Target.Name = "PivotMPM" Then Application.EnableEvents = False Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set dest = PivotTables("PivotHW").PivotFields("IT Region") On Error GoTo What_Happened Range("Header") = "" ' You cannot select NOTHING, so first go and turn on the ones we want, then go and turn off the others! For Each pi In Target.PivotFields("IT Region").PivotItems ' Now we set them the same as the other one! If pi.Visible And dest.PivotItems(pi.Name).Visible = False Then dest.PivotItems(pi.Name).Visible = pi.Visible End If If pi.Visible Then Range("Header") = Range("Header") & pi.Name & ", " End If Next pi Range("Header") = Left(Range("Header"), Len(Range("Header")) - 2) For Each pi In Target.PivotFields("IT Region").PivotItems ' Now we set them the same as the other one! If pi.Visible <> dest.PivotItems(pi.Name).Visible Then dest.PivotItems(pi.Name).Visible = pi.Visible End If Next pi End If Done: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Exit Sub What_Happened: MsgBox Err.Description GoTo Done End Sub