CODEWITHSUNDEEP
vbaexcelpivot-table
Nate
Nate

Reputation: 197

Set multiple slicers at once in Excel

I have a workbook with two pivot table that have the exact same slicers. I'm using a VBA script to synchronize the two slicers. I've named them the same with one as "Slicer_xxxx_Master" and the other as "Slicer_xxxx_Slave".

The code below works fine, except on slicers with lots of options. Since it sets the slices one by one, it re-filters the pivot table over and over again until all the required slices are set.

Is there a method to collect all the slice item values into an array and then set the Slave slicer values all at once?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.ScreenUpdating = False

Dim sC As SlicerCache
Dim sC_Slave As SlicerCache
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem
Dim sI_Slave As SlicerItem


For Each sC In ActiveWorkbook.SlicerCaches
    If InStr(1, sC.Name, "Master") Then
        Set sC_Slave = ThisWorkbook.SlicerCaches(Replace(sC.Name, "Master", "Slave"))
        For Each sI In sC.SlicerItems
            If sI.Name <> ("(blank)") Then
                Set sI_Slave = sC_Slave.SlicerItems(sI.Name)
                If sI_Slave.Selected <> sI.Selected Then
                    sI_Slave.Selected = sI.Selected
                End If
            End If
        Next
    End If
Next

Application.ScreenUpdating = True
End Sub

Upvotes: 1

Views: 3655

Answers (2)

Nate
Nate

Reputation: 197

I used both the ManualUpdate = FALSE and the Disconnect/Reconnect function on the slave pivot table only, things are much better than they were. Thanks Jeffrey!

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim sC As SlicerCache
Dim sC_Slave As SlicerCache
Dim sI As SlicerItem
Dim sI_Slave As SlicerItem
Dim wS As Worksheet
Dim pT As PivotTable

Set wS = ThisWorkbook.Worksheets("Totals Pivot")
wS.PivotTables("TotalsPivot").ManualUpdate = True

For Each sC In ThisWorkbook.SlicerCaches
    If InStr(1, sC.Name, "Master") Then
        Set sC_Slave = ThisWorkbook.SlicerCaches(Replace(sC.Name, "Master", "Slave"))
        sC_Slave.PivotTables.RemovePivotTable ("TotalsPivot")

        For Each sI In sC.SlicerItems
            Set sI_Slave = sC_Slave.SlicerItems(sI.Name)
            If sI_Slave.Selected <> sI.Selected Then
                sI_Slave.Selected = sI.Selected
            End If
        Next

        sC_Slave.PivotTables.AddPivotTable wS.PivotTables("TotalsPivot")
    End If
Next

wS.PivotTables("TotalsPivot").ManualUpdate = False

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Upvotes: 2

jeffreyweir
jeffreyweir

Reputation: 4824

If you're using Excel 2013 or later, then you can add each data source to the DataModel, and then just define a relationship between them. Then one slicer will rule them all, and it will be a lot faster than keeping things separate, because OLAP/PowerPivot PivotTables simply get fed an array (VisiblieItemsList) that tells them in one go what to show.

But if you want to keep things separate, then to speed things up when changing the status of PivotItems (be it directly or via a Slicer), at a minimum you should set each PivotTable's .ManualUpdate property to TRUE, to stop the PivotTables updating after each and every SlicerItem is changed.

Given we're dealing with Slicers here, you can really kick the routine into overdrive by instead doing the following (and leaving the .ManualUpdate properties of the Pivots set to FALSE):

  1. Temporarily disconnect the slicer from both Master and Slave
  2. Iterate through the slicer cache like you are doing above
  3. Reconnect the slicer to both Master and Slave.

The reason this is much faster again is that there seems to be a bug in the way Slicers are implemented, in that any time you change the status of a SlicerItem it causes the PivotTable to update...even if you have set the PivotTables .ManualUpdate setting to true, as covered in a blogpost I wrote here

I've written a postcovering bottlenecks when programming PivotTable here and that might be of interest, as well as quite a few answers to questions here at SO that might be worth a look, such as this one.

Upvotes: 1

Related Questions