Sub SelectSlicerItemAndFilterPivotTable()
Dim ws As Worksheet
Dim sc As SlicerCache
Dim si As SlicerItem
Dim pt As PivotTable
Dim itemFound As Boolean
Dim sItem As String
Dim slicerName As String
Dim pivotTableName As String
Dim ptField As Range
' Initialize variables
sItem = "C" ' The item you want to select
slicerName = "Slicer_Cat" ' The name of the slicer cache
pivotTableName = "PivotTable1" ' The name of the PivotTable
' Set references to worksheet, slicer cache, and PivotTable
Set ws = ThisWorkbook.Worksheets("Sheet4")
Set sc = ThisWorkbook.SlicerCaches(slicerName)
On Error Resume Next
Set pt = ws.PivotTables(pivotTableName)
On Error GoTo 0
' Check if the PivotTable is connected to the slicer
If pt Is Nothing Then
MsgBox "PivotTable '" & pivotTableName & "' not found on worksheet '" & ws.Name & "'.", vbExclamation
GoTo CleanUp
End If
' Clear previous slicer selections
sc.ClearAllFilters
' Find and select the specific slicer item
itemFound = False
For Each si In sc.SlicerItems
If si.Name = sItem Then
si.Selected = True
itemFound = True
Else
si.Selected = False
End If
Next si
' Inform the user if the slicer item was not found
If Not itemFound Then
MsgBox "The slicer item '" & sItem & "' was not found.", vbExclamation
Else
' Refresh the PivotTable to apply the filter
pt.RefreshTable
' Optional: Output filtered data for verification
For Each ptField In pt.DataBodyRange.Rows
Debug.Print ptField.Value
Next ptField
End If
CleanUp:
' Clear object references
Set ws = Nothing
Set sc = Nothing
Set pt = Nothing
Set si = Nothing
Set ptField = Nothing
End Sub
