Synchronizing slicers

Question

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

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

I have two slicers (one per source table). Each slicer controlling a number of associated Pivot Tables.

I would like to be able to synchronize them.

That is if user selects value A in Slicer_1, Slicer_2 gets automatically updated to have value A selected as well.

So what I have so far is very basic

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 got about triggering it automatically when slicer_1 changes ? I have assigned the macro to slicer_2, but the update does not happen until the the slicer box is clicked.

And how do I delay execution until all the changes have been applied. At this time it updates the A field (selected yes/no) refreshes my tables and moves on to B and etc.

I want it to wait with the refresh until all the slicer fields have been updated

Thank you

Answer 1

Synchronizing slicers can be done in a generic way.
With "generic" I mean that there should be no dependency on (literal) slicer cache names and synchronizing could start from any slicer cache.

The approach to bring this all about is by saving state of all slicer cache objects. After a change in a pivot table (underlying one or more slicer caches) new states can be compared with old states and updated caches recognized. From there synchronizing can be accomplished.

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

1) clsWrapperCache, wrapper class around 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 holds an Excel SlicerCache object.
More importantly: it can administer state of a SlicerCache. Obtaining state can be done very fast, i.e. by concatenating:

• the name of the 1st VisibleSlicerItem and
• the number of VisibleSlicerItems.

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

2) clsWrapperCaches, collection class of clsWrapperCache objects

' 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, merely holding clsWrapperCache objects. It will be used for holding objects in the AllCaches collection.

3) clsCacheManager, class for dealing with SlicerCache object states

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

Class clsCacheManager manages cache states with methods Set_Caches and Synchronize_Caches.
Set_Caches: if the number of caches in ThisWorkbook differs from that of AllCaches, AllCaches collection is (re)built. Hereby the OldState of every slicer cache is saved.

Synchronize_Caches: all caches are traversed here. If a slicer cache has been updated (oWCfrom.CurrentState <> oWCfrom.OldState) than any other cache with the same SourceName (e.g. 'year') will also get updated. Updating is by copying all selections of slicer items from source cache to destination cache. OldState for all caches involved is reset to current state at the end of the synchronizing process.

4) ThisWorkbook, 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

Alle benefits from steps 1 to 3 can be reaped in step 4: we can do calls to CacheManager like SetCaches or Synchronize_Caches. This code is easy to read.

Advantages of this solution:

1. works for all slicer caches in a workbook
2. does not depend on SlicerCache names
3. very fast, because states of slicer cache objects are obtained very fast
4. extendable. Class clsCacheManager could be extended for dealing with dependencies between slicer caches.

Answer 2

I came up with the same problem in the past and in my opinion, synchronizing Pivot Table is easier than Slicers.When you connect several Pivot Tables (with same cache) into a Slicer, altering any of those Pivot Tables field (from which you created the Slicer) changes the Slicer Selection as well as the rest of the Pivot Tables.

So for example you have 12 Pivot Tables and 2 Slicers, 6 assigned to 1 and another 6 assigned to the other.
Also let us say you have a common field WorkWeek with the exact same items 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 in the sheet that contains your Target Pivot Tables (PT1 and PT2 in above example).
Take note of the following assumptions for this example:

1. PT1 and PT2 have WorkWeek field on Report Filter (not Rows/Columns).
2. PT1 is linked to Slicer1 and PT2 is linked on Slicer2.
3. No multiple selection is allowed (at least for above set up).

So basically what happens is when you change PT1 WorkWeek selection which is linked to Slicer1,
PT2 changes as well which in turn changes Slicer2 selection as well.
If you change the Slicer1 or 2 selection, the same effect will take place.
Any selection change in Slicer1 will take effect on Slicer2.
This is just the idea. I don't know if you are putting fields on Report Filter or Rows/Columns.
You can adjust the said sample to suit your needs just in case.
To select multiple items, you will have to use a loop to assign and select each of the items. HTH.