'Syncing Multiple PivotTable Slicers using VBA - Improve Code
I am extremely new to VBA code; however, I have found a resource online that has helped me sync my PivotTable Slicers in Excel. The code seems to work exactly how I want it, but it takes approximately ~9-10 seconds to execute. I was hoping the gurus on here would help me clean up the code and perhaps make it more efficient. I also researched turning the .ManualUpdate property to true to increase the speed but I was unable to get the code to work properly. The VBA code I am using is below:
Each PivotTable uses different datasets, but all share the same three items that will be used in the slicer: "Quarter", "Month" and "Component".
Dim sc1 As SlicerCache
Dim sc2 As SlicerCache
Dim sc3 As SlicerCache
Dim sc4 As SlicerCache
Dim sc5 As SlicerCache
Dim sc6 As SlicerCache
Dim sc7 As SlicerCache
Dim sc8 As SlicerCache
Dim sc9 As SlicerCache
Dim SI1 As SlicerItem
Dim sc10 As SlicerCache
Dim sc11 As SlicerCache
Dim sc12 As SlicerCache
Dim sc13 As SlicerCache
Dim sc14 As SlicerCache
Dim sc15 As SlicerCache
Dim sc16 As SlicerCache
Dim sc17 As SlicerCache
Dim sc18 As SlicerCache
Dim SI3 As SlicerItem
Dim sc19 As SlicerCache
Dim sc20 As SlicerCache
Dim sc21 As SlicerCache
Dim sc22 As SlicerCache
Dim sc23 As SlicerCache
Dim sc24 As SlicerCache
Dim sc25 As SlicerCache
Dim sc26 As SlicerCache
Dim sc27 As SlicerCache
Dim SI5 As SlicerItem
' These names come from Slicer Settings dialog box
Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Quarter")
Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Quarter1")
Set sc3 = ThisWorkbook.SlicerCaches("Slicer_Quarter2")
Set sc4 = ThisWorkbook.SlicerCaches("Slicer_Quarter3")
Set sc5 = ThisWorkbook.SlicerCaches("Slicer_Quarter4")
Set sc6 = ThisWorkbook.SlicerCaches("Slicer_Quarter5")
Set sc7 = ThisWorkbook.SlicerCaches("Slicer_Quarter6")
Set sc8 = ThisWorkbook.SlicerCaches("Slicer_Quarter7")
Set sc9 = ThisWorkbook.SlicerCaches("Slicer_Quarter8")
Set sc10 = ThisWorkbook.SlicerCaches("Slicer_Month")
Set sc11 = ThisWorkbook.SlicerCaches("Slicer_Month1")
Set sc12 = ThisWorkbook.SlicerCaches("Slicer_Month2")
Set sc13 = ThisWorkbook.SlicerCaches("Slicer_Month3")
Set sc14 = ThisWorkbook.SlicerCaches("Slicer_Month4")
Set sc15 = ThisWorkbook.SlicerCaches("Slicer_Month5")
Set sc16 = ThisWorkbook.SlicerCaches("Slicer_Month6")
Set sc17 = ThisWorkbook.SlicerCaches("Slicer_Month7")
Set sc18 = ThisWorkbook.SlicerCaches("Slicer_Month8")
Set sc19 = ThisWorkbook.SlicerCaches("Slicer_Component")
Set sc20 = ThisWorkbook.SlicerCaches("Slicer_Component1")
Set sc21 = ThisWorkbook.SlicerCaches("Slicer_Component2")
Set sc22 = ThisWorkbook.SlicerCaches("Slicer_Component3")
Set sc23 = ThisWorkbook.SlicerCaches("Slicer_Component4")
Set sc24 = ThisWorkbook.SlicerCaches("Slicer_Component5")
Set sc25 = ThisWorkbook.SlicerCaches("Slicer_Component6")
Set sc26 = ThisWorkbook.SlicerCaches("Slicer_Component7")
Set sc27 = ThisWorkbook.SlicerCaches("Slicer_Component8")
Application.ScreenUpdating = False
Application.EnableEvents = False
sc2.ClearManualFilter
sc3.ClearManualFilter
sc4.ClearManualFilter
sc5.ClearManualFilter
sc6.ClearManualFilter
sc7.ClearManualFilter
sc8.ClearManualFilter
sc9.ClearManualFilter
sc11.ClearManualFilter
sc12.ClearManualFilter
sc13.ClearManualFilter
sc14.ClearManualFilter
sc15.ClearManualFilter
sc16.ClearManualFilter
sc17.ClearManualFilter
sc18.ClearManualFilter
sc20.ClearManualFilter
sc21.ClearManualFilter
sc22.ClearManualFilter
sc23.ClearManualFilter
sc24.ClearManualFilter
sc25.ClearManualFilter
sc26.ClearManualFilter
sc26.ClearManualFilter
On Error Resume Next
For Each SI1 In sc1.SlicerItems
sc2.SlicerItems(SI1.Name).Selected = SI1.Selected
sc3.SlicerItems(SI1.Name).Selected = SI1.Selected
sc4.SlicerItems(SI1.Name).Selected = SI1.Selected
sc5.SlicerItems(SI1.Name).Selected = SI1.Selected
sc6.SlicerItems(SI1.Name).Selected = SI1.Selected
sc7.SlicerItems(SI1.Name).Selected = SI1.Selected
sc8.SlicerItems(SI1.Name).Selected = SI1.Selected
sc9.SlicerItems(SI1.Name).Selected = SI1.Selected
Next SI1
For Each SI3 In sc10.SlicerItems
sc11.SlicerItems(SI3.Name).Selected = SI3.Selected
sc12.SlicerItems(SI3.Name).Selected = SI3.Selected
sc13.SlicerItems(SI3.Name).Selected = SI3.Selected
sc14.SlicerItems(SI3.Name).Selected = SI3.Selected
sc15.SlicerItems(SI3.Name).Selected = SI3.Selected
sc16.SlicerItems(SI3.Name).Selected = SI3.Selected
sc17.SlicerItems(SI3.Name).Selected = SI3.Selected
sc18.SlicerItems(SI3.Name).Selected = SI3.Selected
Next SI3
For Each SI5 In sc19.SlicerItems
sc20.SlicerItems(SI5.Name).Selected = SI5.Selected
sc21.SlicerItems(SI5.Name).Selected = SI5.Selected
sc22.SlicerItems(SI5.Name).Selected = SI5.Selected
sc23.SlicerItems(SI5.Name).Selected = SI5.Selected
sc24.SlicerItems(SI5.Name).Selected = SI5.Selected
sc25.SlicerItems(SI5.Name).Selected = SI5.Selected
sc26.SlicerItems(SI5.Name).Selected = SI5.Selected
sc27.SlicerItems(SI5.Name).Selected = SI5.Selected
Next SI5
On Error GoTo 0
MsgBox "Update Complete"
clean_up:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
err_handle:
MsgBox Err.Description
Resume clean_up
End Sub```
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
