Error on VBA Code to Loop Through Slicer Items

Anniemack

New Member
Joined
Oct 11, 2018
Messages
19
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.

VBA Code:
    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!
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
added note: I've mixed versions of this loop. Here is a better version of the code:

VBA 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
 
Upvote 0
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.
 
Upvote 0
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.
1614289067457.png

I'm not sure what you mean by manually? No, the sheet is not protected.
 
Upvote 0
By manually i mean can you replicate these steps on the workbook itself. Not using the code but deselecting and selecting the slicer items.
 
Upvote 0
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?
 
Upvote 0
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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