Run Time error 5

J

JPerkins26

New Member
Joined
Aug 20, 2021
Messages
4
Office Version
  1. 365
Platform
  1. 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.

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:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Try replacing:

VBA Code: 
    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

with the following:

VBA Code: 
    On Error Resume Next
'
    For Each SI1 In sc1.SlicerItems
        If Not sc2.SlicerItems(SI1.Name) Is Nothing Then sc2.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc3.SlicerItems(SI1.Name) Is Nothing Then sc3.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc4.SlicerItems(SI1.Name) Is Nothing Then sc4.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc5.SlicerItems(SI1.Name) Is Nothing Then sc5.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc6.SlicerItems(SI1.Name) Is Nothing Then sc6.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc7.SlicerItems(SI1.Name) Is Nothing Then sc7.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc8.SlicerItems(SI1.Name) Is Nothing Then sc8.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc9.SlicerItems(SI1.Name) Is Nothing Then sc9.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc10.SlicerItems(SI1.Name) Is Nothing Then sc10.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc11.SlicerItems(SI1.Name) Is Nothing Then sc11.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc12.SlicerItems(SI1.Name) Is Nothing Then sc12.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc13.SlicerItems(SI1.Name) Is Nothing Then sc13.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc14.SlicerItems(SI1.Name) Is Nothing Then sc14.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc15.SlicerItems(SI1.Name) Is Nothing Then sc15.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc16.SlicerItems(SI1.Name) Is Nothing Then sc16.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc17.SlicerItems(SI1.Name) Is Nothing Then sc17.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc18.SlicerItems(SI1.Name) Is Nothing Then sc18.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc19.SlicerItems(SI1.Name) Is Nothing Then sc19.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc20.SlicerItems(SI1.Name) Is Nothing Then sc20.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc21.SlicerItems(SI1.Name) Is Nothing Then sc21.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc22.SlicerItems(SI1.Name) Is Nothing Then sc22.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc23.SlicerItems(SI1.Name) Is Nothing Then sc23.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc24.SlicerItems(SI1.Name) Is Nothing Then sc24.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc25.SlicerItems(SI1.Name) Is Nothing Then sc25.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc26.SlicerItems(SI1.Name) Is Nothing Then sc26.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc27.SlicerItems(SI1.Name) Is Nothing Then sc27.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc28.SlicerItems(SI1.Name) Is Nothing Then sc28.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc29.SlicerItems(SI1.Name) Is Nothing Then sc29.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc30.SlicerItems(SI1.Name) Is Nothing Then sc30.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc31.SlicerItems(SI1.Name) Is Nothing Then sc31.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc32.SlicerItems(SI1.Name) Is Nothing Then sc32.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc33.SlicerItems(SI1.Name) Is Nothing Then sc33.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc34.SlicerItems(SI1.Name) Is Nothing Then sc34.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc35.SlicerItems(SI1.Name) Is Nothing Then sc35.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc36.SlicerItems(SI1.Name) Is Nothing Then sc36.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc37.SlicerItems(SI1.Name) Is Nothing Then sc37.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc38.SlicerItems(SI1.Name) Is Nothing Then sc38.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc39.SlicerItems(SI1.Name) Is Nothing Then sc39.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc40.SlicerItems(SI1.Name) Is Nothing Then sc40.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc41.SlicerItems(SI1.Name) Is Nothing Then sc41.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc42.SlicerItems(SI1.Name) Is Nothing Then sc42.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc43.SlicerItems(SI1.Name) Is Nothing Then sc43.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc44.SlicerItems(SI1.Name) Is Nothing Then sc44.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc45.SlicerItems(SI1.Name) Is Nothing Then sc45.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc46.SlicerItems(SI1.Name) Is Nothing Then sc46.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc47.SlicerItems(SI1.Name) Is Nothing Then sc47.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc48.SlicerItems(SI1.Name) Is Nothing Then sc48.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc49.SlicerItems(SI1.Name) Is Nothing Then sc49.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc50.SlicerItems(SI1.Name) Is Nothing Then sc50.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc51.SlicerItems(SI1.Name) Is Nothing Then sc51.SlicerItems(SI1.Name).Selected = SI1.Selected
        If Not sc52.SlicerItems(SI1.Name) Is Nothing Then sc52.SlicerItems(SI1.Name).Selected = SI1.Selected
    Next SI1
'
    On Error GoTo 0
 
Upvote 0
You could try something like this:

VBA Code: 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Dim sc1 As SlicerCache
    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Loan_Type")
    
   Dim n As Long
   For n = 2 To 52
        
        With ThisWorkbook.SlicerCaches("Slicer_Loan_Type" & n - 1)
        
            .ClearManualFilter
            Dim SI1 As SlicerItem
            On Error Resume Next
            For Each SI1 In sc1.SlicerItems
                .SlicerItems(SI1.Name).Selected = SI1.Selected
            Next SI1
            On Error GoTo 0
        End With
   Next n
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub
 
Upvote 0
Thank you guys. I moved everything into power pivot and linked the slicers thru data models. I appreciate the help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Changing slicer value
Replies
1
Views
401
sijpie
sijpie
sashapixie
  • Question
Synch slicers VBA - object defined error
Replies
3
Views
785
DanteAmor
DanteAmor
A
  • Question
VBA scripting: looping through slicers and 1 item in each
Replies
0
Views
1K
AcidKid
A
L
  • Question
VBA Code to Sync Slicers
Replies
0
Views
1K
Langtn02
L
R
  • Question
VBA Multiple Slicers
Replies
0
Views
570
Rik76
R

Forum statistics

Threads
1,215,447
Messages
6,124,907
Members
449,194
Latest member
JayEggleton

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top