Speeding up slicer sync w/multiple data sources

[vgfiring]

I have an identical situation to what Mr. Jerry Sullivan has solved in this previous thread: pivot table question (and in turn, this: Pivot Table- "Master" Report Filter).

I have a button that initiates the vba to sync all slicers and their respective pivot tables located in a "filtered tables" tab, with a master pivot table that has slicers located on a dashboard.
This is working as designed, but the process is too slow for it's intended use.

Is there any way to only sync the slicers or slicer items that actually have been changed to speed it up?
Or any other ideas on how to speed the code up?

Here is what is implemented (with four pivot tables in "Filtered Tables"):

Dashboard:

Sub UpdateButton_Click()
    Dim sMaster As String, sField As String
    Dim CurrentSlicer As Slicer

    sMaster = "MasterTable"

    With Worksheets("Filtered Tables")

        On Error GoTo CleanUp
        Application.EnableEvents = False

        ' sFields are current slicers connected to the "master filter" pivot table (on the Dashboard tab right now)
        ' that the slicers on Filtered Tables tab (and corresponding pivot tables) are being synced with.

        sField = "Slicer1"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)
            
        sField = "Slicer2"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

        sField = "Slicer3"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

        sField = "Slicer4"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

        sField = "Slicer5"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

        sField = "Slicer6"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

        sField = "Slicer7"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

        sField = "Slicer8"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

        sField = "Slicer9"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

        sField = "Slicer10"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

        sField = "Slicer11"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

        sField = "Slicer12"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)

    End With
CleanUp:
    Application.EnableEvents = True
End Sub

Module:

Public Function Synch_All_PT_Filters_BasedOn(PT As PivotTable, _
sField As String)
    Dim PT2 As PivotTable
    Dim vItems As Variant
    
    '---Stores the visible items in an array
    vItems = Store_PT_FilterItems(PT, sField)
    
    '---make array of visible items in PT
    For Each PT2 In Worksheets("Filtered Tables").PivotTables
        If PT2.Name <> PT.Name Then
        '---Applies same filter items to each PivotTable
            Call Filter_PivotField( _
                pvtField:=PT2.PivotFields(sField), _
                    vItems:=vItems)
        End If
    Next PT2
End Function

Private Function Store_PT_FilterItems(PT As PivotTable, _
        sField As String) As Variant
'---Stores visible items in PivotField sField in an array
    Dim sVisibleItems() As String
    Dim pviItem As PivotItem
    Dim i As Long
    
    With PT.PivotFields(sField)
        If .Orientation = xlPageField And _
            .EnableMultiplePageItems = False Then
                ReDim sVisibleItems(1)
                sVisibleItems(0) = .CurrentPage
        Else
            For Each pviItem In .PivotItems
                If pviItem.Visible Then
                    i = i + 1
                    ReDim Preserve sVisibleItems(i)
                    sVisibleItems(i - 1) = pviItem
                End If
            Next
        End If
    End With
    Store_PT_FilterItems = sVisibleItems
End Function

Private Function Filter_PivotField(pvtField As PivotField, _
        vItems As Variant)
'---Filters the PivotField to make stored vItems Visible
    Dim sItem As String, bTemp As Boolean, i As Long
    On Error Resume Next
         Application.ScreenUpdating = False
         Application.Calculation = xlCalculationManual
         If Not (IsArray(vItems)) Then
            vItems = Array(vItems)
    End If
 
    With pvtField
        .Parent.ManualUpdate = True
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        If vItems(0) = "(All)" Then
            For i = 1 To .PivotItems.Count
                If Not .PivotItems(i).Visible Then _
                    .PivotItems(i).Visible = True
            Next i
        Else
            For i = LBound(vItems) To UBound(vItems)
                bTemp = Not (IsError(.PivotItems(vItems(i)).Visible))
                If bTemp Then
                    sItem = .PivotItems(vItems(i))
                    Exit For
                End If
            Next i
            If sItem = "" Then
                MsgBox "None of filter list items found."
                GoTo CleanUp
            End If
            .PivotItems(sItem).Visible = True
            For i = 1 To .PivotItems.Count
                If IsError(Application.Match(.PivotItems(i), _
                    vItems, 0)) = .PivotItems(i).Visible Then
                    .PivotItems(i).Visible = Not (.PivotItems(i).Visible)
                End If
            Next i
        End If
    End With
    
CleanUp:
    pvtField.Parent.ManualUpdate = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

 

[vgfiring]

I will further specify that the code is currently using from 20 to 30 seconds every time the slicers are being synced.

The number of pivot items ranges from 2 to 141 at the most for each slicer, at a total of 300-350 pivot items.

I know already that the most amount of time is consumed from applying/copying the stored visible pivot items from the 'master' slicers to the 'slaves'.
In the code, this is where the time consuming magic happens

For i = 1 To .PivotItems.Count
                If IsError(Application.Match(.PivotItems(i), _
                    vItems, 0)) = .PivotItems(i).Visible Then
                    .PivotItems(i).Visible = Not (.PivotItems(i).Visible)
                End If
            Next i

My hope is to limit how many times this code has to run, to reduce process speed.

Grateful for any help!

 

[Jerry Sullivan]

Welcome to MrExcel,

The code that you used as a starting point was designed to handle the scenarios where Slicers are not in use. That's necessary for users of Excel versions prior to xl2010 or when the PivotTables to be synced do not share the same datasource or PivotCache.

What version of Excel are you using?
Are all the PivotTables to be synced connected to an Excel Slicer?

If so, you only need to apply the filtering to one of those PivotTables.

There are some techniques that can further speed the process, but I'll await your response to the two questions as those will affect your options.

 

[vgfiring]

Double post

 

[vgfiring]

I am using xl2010.

The pivot tables to be synced are indeed not sharing the same data source/pivot cache. There are four different sources/caches. Each with multiple pivot tables connected to them that should be synced.

 

[Jerry Sullivan]

There is some opportunity for savings there. Let's say you have 20 PivotTables on Sheet "Filtered Tables" consisting of 4 "sets" of 5 PivotTables that each share the same PivotCache and have connected Slicers for the 12 fields to be synced.

The current code stores the visible items in the Master, then updates 20 pivots separately. One improvement would be to just sync one PivotTable from each of the 4 sets.

Would that work, or am I missing something about your setup that requires each of the PivotTables to be filtered separately?

Is there any way to only sync the slicers or slicer items that actually have been changed to speed it up?

You could store and maintain lists of PivotItems that are Visible in each of the 12 Slicer Fields the last time the macro was run.
The next time the macro is run, the currently Visible PivotItems in the Master could be compared to the stored list for each field. The macro would then only update the Slave PivotTable on Sheet "Filtered Tables" for those fields that have had changes.

There's two potential problems with this approach. If the Slave PivotTables have had their filters changed through some method other than the use of this macro, the macro could skip syncing them if the Master PivotTable items have not been changed.

Similarly, if any of the Slave PivotTables have had PivotItems added to the fields since the last sync with the Master, the macro could skip syncing those. This chance of this latter problem occurring can be reduced by setting the Field Options to not add new items to the manual filters, however that can be overridden if the manual filters are cleared before a PivotCache refresh occurs.

If you know that the Slave PivotTables' filters will not be made out of sync through one of those two methods, then that approach could significantly improve the speed.

 

[vgfiring]

You're right. In my situation there could be problems like you describe. Better lay off storing visible pivot items.

Your suggestion of just changing the caches instead of all pivot tables is great. I have tried - and failed - in my first attempts now, but i'll keep at it. If you have a suggestion to some vba to have the suggested effect I would apprieciate it immensly!

 

[Jerry Sullivan]

Here's a replacement for the Synch_All_PT_Filters_BasedOn function that you can try.
When multiple PivotTables share the same PivotCache, it will only filter one of those PivotTables on the assumption that the fields are connected by Slicers.
There's no checking on that, so if a field that could be connected by a slicer is not connected, it could be skipped.

Public Function Synch_All_PT_Filters_BasedOn(PT As PivotTable, _
   sField As String)

 Dim bCacheIndexFiltered() As Boolean
 Dim PT2 As PivotTable
 Dim vItems As Variant
 
 ReDim bCacheIndexFiltered(1 To ActiveWorkbook.PivotCaches.Count)
 
 '---Stores the visible items in an array
 vItems = Store_PT_FilterItems(PT, sField)
 
 For Each PT2 In Worksheets("Filtered Tables").PivotTables
   '--check if this pivotcache has already been filtered
   '    assumes field sharing pivotcache are connected by slicer
   If Not bCacheIndexFiltered(PT2.CacheIndex) Then
      bCacheIndexFiltered(PT2.CacheIndex) = True
      '---Applies same filter items to each PivotTable
      Call Filter_PivotField( _
        pvtField:=PT2.PivotFields(sField), _
         vItems:=vItems)
   End If
 Next PT2
 
End Function