Error on VBA Code to Loop Through Slicer Items

[Anniemack]

After looking through posts I thought I had good code to loop through slicer items to find the one I want, and make it selected, otherwise deselect it. But I keep getting Error 1004 on the "For Each si" line.

    Dim sc As SlicerCache, si As SlicerItem

    Set sc = ActiveWorkbook.SlicerCaches("Slicer_BU")
  
    sc.ClearAllFilters
    For Each si In sc.SlicerItems
        If si.Name = "ADS" Then
            sc.SlicerItems(i).Selected = True
        Else
            sc.SlicerItems(i).Selected = False
        End If
    Next

What am I doing wrong?

Thank you!

 

[Anniemack]

added note: I've mixed versions of this loop. Here is a better version of the code:

    Dim sc As SlicerCache, si As SlicerItem, Slicer_BU As Slicer

    Set sc = ActiveWorkbook.SlicerCaches("Slicer_BU")
   
    sc.ClearAllFilters
    For Each si In sc.SlicerItems
        If si.Name = "ADS" Then
            si.Selected = True
        Else
            si.Selected = False
        End If
    Next

 

[steve the fish]

What is the error message bar 1004? Did you try do this manually? You will get a 1004 if the sheet is protected for example.

 

[Anniemack]

What is the error message bar 1004? Did you try do this manually? You will get a 1004 if the sheet is protected for example.

I'm not sure what you mean by manually? No, the sheet is not protected.

 

[steve the fish]

By manually i mean can you replicate these steps on the workbook itself. Not using the code but deselecting and selecting the slicer items.

 

[Anniemack]

Oh, yes, the slicer works without issue.

 

[steve the fish]

Hmm. That code works perfectly well on my excel version. If you use the macro recorder recording you doing some selecting/deselecting what does it produce code wise?

 

[Anniemack]

Sub Macro8()

    ActiveWorkbook.SlicerCaches("Slicer_BU").VisibleSlicerItemsList = Array( _
        "[OBUs].[BU].&[ATS]")
    ActiveWorkbook.SlicerCaches("Slicer_BU").VisibleSlicerItemsList = Array( _
        "[OBUs].[BU].&[ADS]")
    ActiveWorkbook.SlicerCaches("Slicer_BU").ClearManualFilter
End Sub

 

[steve the fish]

And you can run that code you just provided without issue?

 

[Anniemack]

Yes, it runs and selects the correct items, then clears the slicer.

I was trying to move away from arrays because I don't really know how to work with arrays.