VBA for clearing slicers

[Filip Vermote]

Hello,

to clear slicer settings, I need to follow the underneath VBA code:

Sub slicersettings()

Dim Atelier As String
Dim Kostenstelle As String
Dim Bereik As String
Dim Direktindirekt As String
Atelier = Range("Atelier").Value
Kostenstelle = Range("Kostenstelle").Value
Bereik = Range("Bereich").Value
Direktindirekt = Range("Direktindirekt").Value
ActiveWorkbook.SlicerCaches("Slicer_AT").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_Expl").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_Bereik").ClearManualFilter
With ActiveWorkbook.SlicerCaches("Slicer_AT")
.SlicerItems("5").Selected = False
.SlicerItems("7").Selected = False
.SlicerItems("10").Selected = False
.SlicerItems("11").Selected = False
.SlicerItems("12").Selected = False
.SlicerItems("13").Selected = False
.SlicerItems("14").Selected = False
.SlicerItems("17").Selected = False
.SlicerItems("18").Selected = False
.SlicerItems("19").Selected = False
.SlicerItems("20").Selected = False
.SlicerItems("21").Selected = False
.SlicerItems("22").Selected = False
.SlicerItems("23").Selected = False
.SlicerItems("24").Selected = False
.SlicerItems("25").Selected = False
.SlicerItems("29").Selected = False
.SlicerItems("34").Selected = False
.SlicerItems("35").Selected = False
.SlicerItems("39").Selected = False
.SlicerItems("40").Selected = False
.SlicerItems("41").Selected = False
.SlicerItems("42").Selected = False
.SlicerItems("43").Selected = False
.SlicerItems("44").Selected = False
.SlicerItems("45").Selected = False
.SlicerItems("46").Selected = False
.SlicerItems("47").Selected = False
.SlicerItems("48").Selected = False
.SlicerItems("49").Selected = False
.SlicerItems("57").Selected = False
.SlicerItems("59").Selected = False
.SlicerItems("67").Selected = False
.SlicerItems("69").Selected = False
.SlicerItems("70").Selected = False
.SlicerItems("75").Selected = False
.SlicerItems("90").Selected = False
.SlicerItems("94").Selected = False
.SlicerItems("97").Selected = False
.SlicerItems(Atelier).Selected = True
End With

End sub ()

I firstly need to deselect all items, the last row shows the only seleted item.
Is there a code to deselect all items in one instruction unconditionally? When an item not listed is not in the pivot table, the VBA gives an error.

 

[Domenic]

I have amended your code so that it first checks whether the target slicer item exists. If so, then it loops through each slicer item to de-select them, except the target slicer item.

Sub slicersettings()

    Dim Atelier As String
    Dim Kostenstelle As String
    Dim Bereik As String
    Dim Direktindirekt As String
    Dim targetSlicerItem As SlicerItem
    Dim currentSlicerItem As SlicerItem
    
    Atelier = Range("Atelier").Value
    Kostenstelle = Range("Kostenstelle").Value
    Bereik = Range("Bereich").Value
    Direktindirekt = Range("Direktindirekt").Value
    
    ActiveWorkbook.SlicerCaches("Slicer_AT").ClearManualFilter
    ActiveWorkbook.SlicerCaches("Slicer_Expl").ClearManualFilter
    ActiveWorkbook.SlicerCaches("Slicer_Bereik").ClearManualFilter
    
    With ActiveWorkbook.SlicerCaches("Slicer_AT")
        On Error Resume Next
        Set targetSlicerItem = .SlicerItems(Atelier)
        On Error GoTo 0
        If Not targetSlicerItem Is Nothing Then
            For Each currentSlicerItem In .SlicerItems
                If currentSlicerItem.Name <> Atelier Then
                    currentSlicerItem.Selected = False
                End If
            Next currentSlicerItem
        End If
    End With

End Sub

Hope this helps!