Subscribe
in a readerSynchronising Slicers
Oftentimes a reporting workbook contains more than one pivottable. As I showed you already, synchronising pivottables that use the same pivotcache is very easy if you tie them together with one or more slicers pointing to both pivottables. You use the "Pivottable Connections" button on the ribbon for that.
However, it frequently happens that pivottables do not share a pivotcache, but still need to be in sync, for instance if your slicer is filtering on a reporting period such as Year, Quarter or Month. In the code below I assume all pivotcaches share the fieldnames to filter on. I also assume the tied-together slicercaches share the same values, in other words, all years appearing in Slicer_Year also appear in Slicer_Year1 and vice versa. If your situation differs (values in slicercache 1 might be missing in slicercache 2 or the other way around), you will need to take special precautions.
Setting things up
The first action needed is to make sure each slicer is connected to all relevant pivottables belonging to its pivotcache. Take care NOT to add a pivottable to a slicer for which the field in question is not used in that pivottable, because a slicer can operate on a field that isn't included in the pivottable itself. It will filter your pivottable and the pivot itself gives no clue that it was filtered (other than the slicer).
Suppose you have three reports: Monthly, Quartely and Yearly. You might have three slicers for those pivottables: Year, Month and Quarter. But for the quarterly and yearly pivottable, it makes no sense to tie them to the Month slicer. In fact, it would turn them all into a monthly report. In a situation like this, only the Year slicer is a sensible one to connect to all three pivottables. If you would want to connect the Quarter slicer to the Month slicer, you would need some VBA which, after selecting a month, sets the associated Quarter in the Quarter slicer. The code below isn't that smart, it just syncs Year, Quarter and Month slicers, assuming you did all of the slicers pivottable connections correctly.
Triggering a change in a slicer filter
There is no event tied to slicer actions directly. However, clicking a slicer triggers the Workbook_SheetPivotTableUpdate event in the ThisWorkbook module (there is a similar event in the sheet modules, but I want one generic event which responds to any pivottable update). The empty event stub looks like this:
End Sub
Sh is an object variable pointing to the worksheet on which a pivottable just updated. Target is an object variable pointing to the pivottable that just updated.
The first thing the code needs to do is find out which slicers are connected to the pivottable that just changed and set a pointer to the ones we want to use to sync the other pivottables to.
First, we declare some variables:
Dim oScKwrt As SlicerCache
Dim oScYear As SlicerCache
Dim oSc As SlicerCache
Dim oPT As PivotTable
Dim oSi As SlicerItem
The first three are going to hold the Year, Quarter and Month slicers tied to the changed pivottable. Note that no more than two of these three are in fact tied to the pivottable: either Year and Month, Year and Quarter or just Year.
Finding the slicer that was clicked
So now step through all SlicerCaches and then through all their associated pivottables to find out which slicers are tied to the changed pivottable:
For Each oPT In oSc.PivotTables
If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
If oSc.Name Like "*Year*" Then
Set oScYear = oSc
ElseIf oSc.Name Like "*Month*" Then
Set oScMonth = oSc
ElseIf oSc.Name Like "*Quarter*" Then
Set oScKwrt = oSc
End If
Exit For
End If
Next
If Not oScYear Is Nothing And Not oScMonth Is Nothing And Not oScKwrt Is Nothing Then Exit For
Next
As you can see, we only pick up the slicers Month, Quarter and Year because those are the ones we want to sync.
Synchronising the slicers
Now that we have the slicer(s) that have been clicked, we want to sync them with slicers with similar names. By Default, Excel names SlicerCaches like this: Slicer_FieldName. If another slicer with the same fieldname is added (which adds a SlicerCache too), it is called Slicer_FieldName1, the next one is called Slicer_FieldName2 and etcetera. So what the code below does is loop through all slicercaches again, looking at their names to figure out which ones belong together.
The code has a special trick to avoid problems. Suppose you have a slicer which has its first item selected and the remaining items de-selected. Suppose that the sister slicer is clicked and we click the second item. When the code runs through all slicer items, it will start with the first one, which will be de-selected. This causes the slicer to select all items, because it is not allowed to de-select all items of a slicer. Subsequently the code runs through the remaining items and sets them as needed. The end result is that both item 1 and item 2 are selected, rather than just item 2. The trick around this is setting the last slicer item to "Selected" before running through the loop:
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScYear.Name, 7, 3) And oSc.Name <> oScYear.Name Then
'This one has a similar fieldname (first three characters are compared in this case)
'but not the same name, as that would be the same slicercache.
'So synch it with the changed year slicer
'If a slicer has the very first item selected and you subsequently de-select it,
'the end result is that all sliceritems get selected. So select the last item of the slicer first
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScYear.SlicerItems
On Error Resume Next
If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
End If
Next
End If
Next
End If
Putting it all together
So if you put all of this together, this is what you end up with:
Dim mbNoEvent As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim oScMonth As SlicerCache
Dim oScKwrt As SlicerCache
Dim oScYear As SlicerCache
Dim oSc As SlicerCache
Dim oPT As PivotTable
Dim oSi As SlicerItem
Dim sYear As String
Dim bUpdate As Boolean
'Prevent event looping, changing a slicer in this routine also triggers this routine
If mbNoEvent Then Exit Sub
mbNoEvent = True
bUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each oSc In ThisWorkbook.SlicerCaches
For Each oPT In oSc.PivotTables
If oPT.Name = Target.Name And oPT.Parent.Name = Target.Parent.Name Then
If oSc.Name Like "*Year*" Then
Set oScYear = oSc
ElseIf oSc.Name Like "*Month*" Then
Set oScMonth = oSc
ElseIf oSc.Name Like "*Quarter*" Then
Set oScKwrt = oSc
End If
Exit For
End If
Next
If Not oScYear Is Nothing And Not oScMonth Is Nothing And Not oScKwrt Is Nothing Then Exit For
Next
If Not oScYear Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScYear.Name, 7, 3) And oSc.Name <> oScYear.Name Then
'This one has a similar fieldname (first three characters are compared in this case)
'but not the same name, as that would be the same slicercache.
'So synch it with the changed year slicer
'If a slicer has the very first item selected and you subsequently de-select it,
'the end result is that all sliceritems get selected. So select the last item of the slicer first
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScYear.SlicerItems
On Error Resume Next
If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
End If
Next
End If
Next
End If
If Not oScKwrt Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScKwrt.Name, 7, 3) And oSc.Name <> oScKwrt.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScKwrt.SlicerItems
On Error Resume Next
If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
End If
Next
End If
Next
End If
If Not oScMonth Is Nothing Then
For Each oSc In ThisWorkbook.SlicerCaches
If Mid(oSc.Name, 7, 3) = Mid(oScMonth.Name, 7, 3) And oSc.Name <> oScMonth.Name Then
oSc.SlicerItems(oSc.SlicerItems.Count).Selected = True
For Each oSi In oScMonth.SlicerItems
On Error Resume Next
If oSc.SlicerItems(oSi.Value).Selected <> oSi.Selected Then
oSc.SlicerItems(oSi.Value).Selected = oSi.Selected
End If
Next
End If
Next
End If
mbNoEvent = False
Application.ScreenUpdating = bUpdate
End Sub
Next: Showing Selected Slicer Items On a Worksheet
Comments
Showing last 8 comments of 75 in total (Show All Comments):Comment by: Jan Karel Pieterse (5-7-2017 11:12:49)
Yes, that would make this quite slow unfortunately. Especially because the pivottables will refresh at each change in the loop.
Comment by: Abdul (3-10-2017 11:44:29)
when I loop it keep on working with all items which are not in the slicer?
Comment by: Jan Karel Pieterse (3-10-2017 16:53:12)
Please show me the relevant piece of code?
Comment by: Kevin Clarke (17-5-2018 21:28:13)
For Each pt In dstSlicer.PivotTables
pt.ManualUpdate = True
Next pt
... do the work
For Each pt In dstSlicer.PivotTables
pt.ManualUpdate = False
Next pt
Comment by: Jan Karel Pieterse (18-5-2018 11:41:28)
Thanks!
Comment by: Sohaib Aumer (17-7-2018 17:38:55)
Need help. I have 3 slicers in my dashboard, 1 filtering the pivot charts and other 2, non-pivot-tables. All 3 of those have different data sources and data, just a "city" column in common with the same cities.
How can i use vba code to connect other 2 slicers to my 1st slicer.
Regards, Sohaib
Comment by: Jan Karel Pieterse (18-7-2018 10:35:11)
I think this can be pulled off, but I need some sample data. Can you email a sanitized copy of your file please?
Comment by: Jan Karel Pieterse (18-7-2018 11:23:08)
I pulled this off my adding this code to the ThisWorkbook module:
Public NoEvents As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
Dim sSelected() As String
Dim lCt As Long
Dim oScPT As SlicerCache
Dim oSi As SlicerItem
ReDim sSelected(1 To 1)
Set oScPT = ThisWorkbook.SlicerCaches("Slicer_City")
If NoEvents Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
NoEvents = True
For Each oSi In oScPT.SlicerCacheLevels(1).SlicerItems
If oSi.Selected Then
lCt = lCt + 1
ReDim Preserve sSelected(1 To lCt)
sSelected(lCt) = oSi.Value
End If
Next
SyncSlicer sSelected, "Slicer_City1"
SyncSlicer sSelected, "Slicer_City2"
NoEvents = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Sub SyncSlicer(sSelected() As String, sSlicerName As String)
Dim oScTable As SlicerCache
Dim oSi As SlicerItem
Dim lCt As Long
Dim bShow As Boolean
Set oScTable = ThisWorkbook.SlicerCaches(sSlicerName)
For Each oSi In oScTable.SlicerItems
oScTable.SlicerItems(sSelected(1)).Selected = True
bShow = False
For lCt = 1 To UBound(sSelected)
If oSi.Name = sSelected(lCt) Then
bShow = True
Exit For
End If
Next
If oSi.Selected <> bShow Then
oSi.Selected = bShow
End If
Next
End Sub
Have a question, comment or suggestion? Then please use this form.
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.