Select only a single slicer item in a slicer

Question

I have a question regarding the slicers of a Pivot table. Tried several things but it is not working. I am really sorry if this question has been asked before, I did not find a solution until now.

So I want to dis-select all selected slicer items inside a certain pivot table and then select one specific which is stored inside a variable.

Here is my last code:

Sub select_slicer()

Dim myval As String
myval = "Value1"

Dim sli As SlicerItem

ThisWorkbook.Sheets("mysheet").Activate
    For Each sli In ActiveWorkbook.SlicerCaches("Slicer_Sales")
        If sli.name = myval Then
         sli.Selected = True
        Else
         sli.Selected = False
        End If
Next sli

    End Sub

All help will be appreciated.

Possible duplicate of Iterate over slicer via VBA and select a single item each time — JvdV

JLCH JLCH · Accepted Answer · 2019-03-08T13:37:47

Untested (works in one of my macros similar to yours):

ThisWorkbook.Sheets("mysheet").Activate
With ActiveWorkbook.SlicerCaches("Slicer_Sales")
For Each sli In .SlicerItems
    If sli.Name = myval Then
     sli.Selected = True
    Else
     sli.Selected = False
End if
Next sli
End With

Select only a single slicer item in a slicer

1 Answers