JPerkins26
New Member
- Joined
- Aug 20, 2021
- Messages
- 4
- Office Version
- 365
- Platform
- Windows
Hello.
I am trying to run a slicer sync VBA and when i run it i get Run-time error 5. The line it shows when I debug shows sc2.SlicerItems(SI1.Name).Selected = SI1.Selected.
I can't seem to figure out what is wrong with it, please help
here is the rest of the code.
I am trying to run a slicer sync VBA and when i run it i get Run-time error 5. The line it shows when I debug shows sc2.SlicerItems(SI1.Name).Selected = SI1.Selected.
I can't seem to figure out what is wrong with it, please help
here is the rest of the code.
VBA Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
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 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 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 sc28 As SlicerCache
Dim sc29 As SlicerCache
Dim sc30 As SlicerCache
Dim sc31 As SlicerCache
Dim sc32 As SlicerCache
Dim sc33 As SlicerCache
Dim sc34 As SlicerCache
Dim sc35 As SlicerCache
Dim sc36 As SlicerCache
Dim sc37 As SlicerCache
Dim sc38 As SlicerCache
Dim sc39 As SlicerCache
Dim sc40 As SlicerCache
Dim sc41 As SlicerCache
Dim sc42 As SlicerCache
Dim sc43 As SlicerCache
Dim sc44 As SlicerCache
Dim sc45 As SlicerCache
Dim sc46 As SlicerCache
Dim sc47 As SlicerCache
Dim sc48 As SlicerCache
Dim sc49 As SlicerCache
Dim sc50 As SlicerCache
Dim sc51 As SlicerCache
Dim sc52 As SlicerCache
Dim SI1 As SlicerItem
' These names come from Slicer settings dialog box
Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type")
Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type1")
Set sc3 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type2")
Set sc4 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type3")
Set sc5 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type4")
Set sc6 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type5")
Set sc7 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type6")
Set sc8 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type7")
Set sc9 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type8")
Set sc10 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type9")
Set sc11 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type10")
Set sc12 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type11")
Set sc13 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type12")
Set sc14 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type13")
Set sc15 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type14")
Set sc16 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type15")
Set sc17 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type16")
Set sc18 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type17")
Set sc19 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type18")
Set sc20 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type19")
Set sc21 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type20")
Set sc22 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type21")
Set sc23 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type22")
Set sc24 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type23")
Set sc25 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type24")
Set sc26 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type25")
Set sc27 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type26")
Set sc28 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type27")
Set sc29 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type28")
Set sc30 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type29")
Set sc31 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type30")
Set sc32 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type31")
Set sc33 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type32")
Set sc34 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type33")
Set sc35 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type34")
Set sc36 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type35")
Set sc37 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type36")
Set sc38 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type37")
Set sc39 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type38")
Set sc40 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type39")
Set sc41 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type40")
Set sc42 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type41")
Set sc43 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type42")
Set sc44 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type43")
Set sc45 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type44")
Set sc46 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type45")
Set sc47 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type46")
Set sc48 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type47")
Set sc49 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type48")
Set sc50 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type49")
Set sc51 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type50")
Set sc52 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type51")
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
sc2.ClearManualFilter
sc3.ClearManualFilter
sc4.ClearManualFilter
sc5.ClearManualFilter
sc6.ClearManualFilter
sc7.ClearManualFilter
sc8.ClearManualFilter
sc9.ClearManualFilter
sc10.ClearManualFilter
sc11.ClearManualFilter
sc12.ClearManualFilter
sc14.ClearManualFilter
sc15.ClearManualFilter
sc16.ClearManualFilter
sc17.ClearManualFilter
sc18.ClearManualFilter
sc19.ClearManualFilter
sc20.ClearManualFilter
sc21.ClearManualFilter
sc22.ClearManualFilter
sc23.ClearManualFilter
sc25.ClearManualFilter
sc26.ClearManualFilter
sc27.ClearManualFilter
sc28.ClearManualFilter
sc29.ClearManualFilter
sc30.ClearManualFilter
sc31.ClearManualFilter
sc32.ClearManualFilter
sc33.ClearManualFilter
sc34.ClearManualFilter
sc35.ClearManualFilter
sc36.ClearManualFilter
sc37.ClearManualFilter
sc38.ClearManualFilter
sc39.ClearManualFilter
sc40.ClearManualFilter
sc41.ClearManualFilter
sc42.ClearManualFilter
sc43.ClearManualFilter
sc44.ClearManualFilter
sc45.ClearManualFilter
sc46.ClearManualFilter
sc47.ClearManualFilter
sc48.ClearManualFilter
sc49.ClearManualFilter
sc50.ClearManualFilter
sc51.ClearManualFilter
sc52.ClearManualFilter
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
sc10.SlicerItems(SI1.Name).Selected = SI1.Selected
sc11.SlicerItems(SI1.Name).Selected = SI1.Selected
sc12.SlicerItems(SI1.Name).Selected = SI1.Selected
sc13.SlicerItems(SI1.Name).Selected = SI1.Selected
sc14.SlicerItems(SI1.Name).Selected = SI1.Selected
sc15.SlicerItems(SI1.Name).Selected = SI1.Selected
sc16.SlicerItems(SI1.Name).Selected = SI1.Selected
sc17.SlicerItems(SI1.Name).Selected = SI1.Selected
sc18.SlicerItems(SI1.Name).Selected = SI1.Selected
sc19.SlicerItems(SI1.Name).Selected = SI1.Selected
sc20.SlicerItems(SI1.Name).Selected = SI1.Selected
sc21.SlicerItems(SI1.Name).Selected = SI1.Selected
sc22.SlicerItems(SI1.Name).Selected = SI1.Selected
sc23.SlicerItems(SI1.Name).Selected = SI1.Selected
sc24.SlicerItems(SI1.Name).Selected = SI1.Selected
sc25.SlicerItems(SI1.Name).Selected = SI1.Selected
sc26.SlicerItems(SI1.Name).Selected = SI1.Selected
sc27.SlicerItems(SI1.Name).Selected = SI1.Selected
sc28.SlicerItems(SI1.Name).Selected = SI1.Selected
sc29.SlicerItems(SI1.Name).Selected = SI1.Selected
sc30.SlicerItems(SI1.Name).Selected = SI1.Selected
sc31.SlicerItems(SI1.Name).Selected = SI1.Selected
sc32.SlicerItems(SI1.Name).Selected = SI1.Selected
sc33.SlicerItems(SI1.Name).Selected = SI1.Selected
sc34.SlicerItems(SI1.Name).Selected = SI1.Selected
sc35.SlicerItems(SI1.Name).Selected = SI1.Selected
sc36.SlicerItems(SI1.Name).Selected = SI1.Selected
sc37.SlicerItems(SI1.Name).Selected = SI1.Selected
sc38.SlicerItems(SI1.Name).Selected = SI1.Selected
sc39.SlicerItems(SI1.Name).Selected = SI1.Selected
sc40.SlicerItems(SI1.Name).Selected = SI1.Selected
sc41.SlicerItems(SI1.Name).Selected = SI1.Selected
sc42.SlicerItems(SI1.Name).Selected = SI1.Selected
sc43.SlicerItems(SI1.Name).Selected = SI1.Selected
sc44.SlicerItems(SI1.Name).Selected = SI1.Selected
sc45.SlicerItems(SI1.Name).Selected = SI1.Selected
sc46.SlicerItems(SI1.Name).Selected = SI1.Selected
sc47.SlicerItems(SI1.Name).Selected = SI1.Selected
sc48.SlicerItems(SI1.Name).Selected = SI1.Selected
sc49.SlicerItems(SI1.Name).Selected = SI1.Selected
sc50.SlicerItems(SI1.Name).Selected = SI1.Selected
sc51.SlicerItems(SI1.Name).Selected = SI1.Selected
sc52.SlicerItems(SI1.Name).Selected = SI1.Selected
Next SI1
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Last edited by a moderator: