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]

Another reason to move away from Arrays was that my next step will be to compare the slicer item not to a string, but to a cell value, so that I can make it more flexible.

Just trying to walk before I run (crawl before walk?).

 

[steve the fish]

What happens if you try:

For Each si In sc.VisibleSlicerItems

 

[Anniemack]

same error

 

[Anniemack]

can you tell me what the syntax Array([OBUs].[BU].&[ATS]") refers to? the "." between the brackets makes it more than just a structured reference, or at least I can't find what that is.

At this point I'll learn arrays to figure this out.

 

[steve the fish]

Maybe you could take a look at:

Accessing the Slicer through VBA