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
Sample files
Download sample Excel workbooks (29 Aug 2019, downloaded 362 times)
Next: Showing Selected Slicer Items On a Worksheet
Comments
Showing last 8 comments of 86 in total (Show All Comments):Comment by: Marcel (21-6-2019 14:30:00)
However clicking on one slicer and synching the matching other slicer takes up to 27-40 seconds depending on which Region in the slicer was selected.
What could be causing this huge amount of time. I have
- About 9000 records, 83 columns
- 11 slicers
- Only 2 slicers are synched with your script
- Dozens of PT's
- All combined in a dashboard
Comment by: Jan Karel Pieterse (21-6-2019 17:08:00)
I'm not sure. But each change in filter will cause an update of all of the attached pivottables, so it'd make sense if it would take a while if there are many pivottables attached to the two slicers.
This could probably be better solved by making sure there is only one slicer to filter on rather than having to synch two!
Comment by: Zak (29-8-2019 14:09:00)
I have a table with 2 slicers and 2 pivot tables with slicers
The 2 pivot tables along with 3 others(these 3 do not have slicers) count items from 2 columns in the original table and then display as graphs
I want the 2 slicers from the table labelled Region and Country to link to the 2 pivot table slicers of the same name
The pivot table slicers are called Slicer_Region and Slicer_Country
The table slicers are called Slicer_Region1 and Slicer_Country1
How can I modify your code to pull this off
Comment by: Jan Karel Pieterse (29-8-2019 15:51:00)
I have created a setup where a pivottable has a slicer named Slicer_Shipcountry1 and a table has a slicer named Slicer_Shipcountry2. To have the PT slicer update the table slicer:
Dim oSl As SlicerItem
Dim oSc1 As SlicerCache
Dim oSc2 As SlicerCache
Dim sSheet1 As String
Dim sSheet2 As String
Dim sFirstSlicerItemText As String
Dim lCt As Long
Dim bFound As Boolean
If mbNoEvents Then Exit Sub
On Error Resume Next 'IN case a slicer item in slicer 1 is not present in slicer 2
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set oSc1 = SlicerCaches("Slicer_Shipcountry1")
Set oSc2 = SlicerCaches("Slicer_Shipcountry2")
mbNoEvents = True
With oSc2
sFirstSlicerItemText = .SlicerItems(1).Name
.SlicerItems(1).Selected = True
For lCt = 2 To oSc2.SlicerItems.Count
.SlicerItems(lCt).Selected = False
Next
End With
With oSc1
For Each oSl In .SlicerItems
If oSl.Name = sFirstSlicerItemText Then
bFound = True
End If
oSc2.SlicerItems(oSl.Caption).Selected = oSl.Selected
Next
End With
If bFound = False Then
'First slicer item was not in second slicer
oSc2.SlicerItems(1).Selected = False
Else
oSc2.SlicerItems(1).Selected = oSc1.SlicerItems(1).Selected
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
mbNoEvents = False
End Sub
Best to hide the table slicer (no code responds to changes on a table slicer) and just copy the pivot table slicer to the sheet where the table is if you need the slicer to also be available from there.
Comment by: Zak (29-8-2019 16:18:00)
I assume if there are multiple slicers on each table type, it would just be more variables and the following code repeated with the new variables, oSc3, oSc4, oSc5, etc
Set oSc1 = SlicerCaches("Slicer_Shipcountry1")
Set oSc2 = SlicerCaches("Slicer_Shipcountry2")
mbNoEvents = True
With oSc2
sFirstSlicerItemText = .SlicerItems(1).Name
.SlicerItems(1).Selected = True
For lCt = 2 To oSc2.SlicerItems.Count
.SlicerItems(lCt).Selected = False
Next
End With
With oSc1
For Each oSl In .SlicerItems
If oSl.Name = sFirstSlicerItemText Then
bFound = True
End If
oSc2.SlicerItems(oSl.Caption).Selected = oSl.Selected
Next
End With
If bFound = False Then
'First slicer item was not in second slicer
oSc2.SlicerItems(1).Selected = False
Else
oSc2.SlicerItems(1).Selected = oSc1.SlicerItems(1).Selected
End If
Comment by: Jan Karel Pieterse (29-8-2019 16:43:00)
Yes that would work. Alternatively you could move the code out of the Pivot event into a normal module, adding the slicernames as arguments. Then you could pass the slicernames as arguments to the new sub.
So the heading of the new sub would look like:
Dim oSl As SlicerItem
Dim sFirstSlicerItemText As String
Dim lCt As Long
Dim bFound As Boolean
On Error Resume Next 'In case a slicer item in slicer 1 is not present in slicer 2
With oSc2
sFirstSlicerItemText = .SlicerItems(1).Name
.SlicerItems(1).Selected = True
For lCt = 2 To oSc2.SlicerItems.Count
.SlicerItems(lCt).Selected = False
Next
End With
With oSc1
For Each oSl In .SlicerItems
If oSl.Name = sFirstSlicerItemText Then
bFound = True
End If
oSc2.SlicerItems(oSl.Caption).Selected = oSl.Selected
Next
End With
If bFound = False Then
'First slicer item was not in second slicer
oSc2.SlicerItems(1).Selected = False
Else
oSc2.SlicerItems(1).Selected = oSc1.SlicerItems(1).Selected
End If
End Sub
ThisWorkbook would then change to a simpler:
Dim mbNoEvents As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
If mbNoEvents Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Synch2Slicers SlicerCaches("Slicer_Shipcountry1"), SlicerCaches("Slicer_Shipcountry2")
'Add more of those above if needed, just make sure you pair the right slicer names in the correct order
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
mbNoEvents = False
End Sub
Comment by: Philip (25-11-2019 11:08:00)
Thank you for all your solutions to complex problems. I have a worksheet setup to hold all slicers in one place which update quite a lot of number pivot tables. I had the same problem with performance. I use a routine on the pivot cache with the master slicers to turn off/on ManualUpdate on all pivot tables in the workbook. Performance is acceptable now. I also show the user that the report is being updated with a form.
Sub PivotTableManualUpdate(bSwitch As Boolean)
Dim oPt As PivotTable
Dim oWb As Workbook
Dim oWs As Worksheet
Set oWb = ActiveWorkbook
For Each oWs In oWb.Worksheets
For Each oPt In oWs.PivotTables
oPt.ManualUpdate = bSwitch
Next oPt
Next oWs
End Sub
Hope this can help anyeone with performance problems.
Comment by: Jan Karel Pieterse (25-11-2019 12:04:00)
Thanks!
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.