VBA to Sync Multiple Slicer Selections On_Change?

[bs0d]

I have multiple slicers on multiple tables. Most of the data is pulled from a database, but some of it includes calculations made in the Excel sheets. It's my understanding, that because they're from multiple source tables, I cannot make all the tables respond to a single slicer selection. At least, using pre-defined methods.

However, I was thinking surely there's a simple VBA solution. Assume I create one "master" slicer. Is there a way I can make all of the tables with slicers on other tabs filter to the selection made in the master using some VBA? All of my tables on other tabs share a common field and values that my master slicer would point to.


Thanks,

 

[bs0d]

I have multiple slicers on multiple tables. Most of the data is pulled from a database, but some of it includes calculations made in the Excel sheets. It's my understanding, that because they're from multiple source tables, I cannot make all the tables respond to a single slicer selection. At least, using pre-defined methods.

However, I was thinking surely there's a simple VBA solution. Assume I create one "master" slicer. Is there a way I can make all of the tables with slicers on other tabs filter to the selection made in the master using some VBA? All of my tables on other tabs share a common field and values that my master slicer would point to.


Thanks,

On the surface, this appears to be what I'm looking for:

Private Sub Worksheet_PivotTableUpdate _
    (ByVal Target As PivotTable)
Dim wb As Workbook
Dim scShort As SlicerCache
Dim scLong As SlicerCache
Dim siShort As SlicerItem
Dim siLong As SlicerItem

On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ThisWorkbook
Set scShort = wb.SlicerCaches("Slicer_City")
Set scLong = wb.SlicerCaches("Slicer_City1")

scLong.ClearManualFilter

For Each siLong In scLong.VisibleSlicerItems
    Set siLong = scLong.SlicerItems(siLong.Name)
    Set siShort = Nothing
    On Error Resume Next
    Set siShort = scShort.SlicerItems(siLong.Name)
    On Error GoTo errHandler
    If Not siShort Is Nothing Then
        If siShort.Selected = True Then
            siLong.Selected = True
        ElseIf siShort.Selected = False Then
            siLong.Selected = False
        End If
    Else
        siLong.Selected = False
    End If
Next siLong

exitHandler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub

errHandler:
    MsgBox "Could not update pivot table"
    Resume exitHandler

End Sub

Source: http://www.contextures.com/excelslicersupdatevba.html

 

[bs0d]

This code isn't working however. A few moments after making a selection, the second slicer filtering does not match the first -- and the first remains as all selected; it does not change to my single click selection.

 

[bs0d]

This code isn't working however. A few moments after making a selection, the second slicer filtering does not match the first -- and the first remains as all selected; it does not change to my single click selection.

No other feedback here so far, but to the person browsing the web that stumbles across this thread seeking the same solution, here's what I'm doing:

The example above assumes slicers are from the same data source. And if so, the code may very well work. What I ultimately needed was filtering of one primary list (via slicer) to also filter other defined tables according to what's selected in the slicer.

In order to trigger an event from a selection in the slicer, the slicer needed to be applied to a pivot table. I was working with defined Excel tables. So I created a pivot table of the field I wanted to apply a slicer to from my primary or master table. Then i used:

Private Sub Worksheet_PivotTableChangeSync(ByVal Target as PivotTable)

End Sub

To capture changes in the slicer. Within that sub, I include a call to a function that will build an array of selected items from the PivotTable (using .PivotItems(x).Visible boolean property).

This gives me the list of items to filter all other tables by. So after the function call in the PivotTableChangeSync sub, I set the filter criteria for each table using the array from the function.