Excel Pivot table slicer selection with VBA Code

[Lars Ole Sørensen]

Hi,

I have a workbook with soccer statistics in a data worksheet. I have made a pivot table from this data sheet. I am wondering if it is possible to select values between 1,00 and 1,50 in a pivot table slicer with VBA code.

I have attached the workbook with this information.

Kindly Regards
Lars Ole

 

[Chihiro]

Something like below. This assumes there's only one SlicerCache in the workbook and one PivotTable in the worksheet. If more than one, specify SlicerCache/PivotTable with name instead of index.

Sub Test()
Dim pvt As PivotTable
Dim sItem As SlicerItem
Set pvt = ThisWorkbook.Worksheets("Pivot Table").PivotTables(1)
pvt.ManualUpdate = True

With ThisWorkbook.SlicerCaches(1)
    .ClearAllFilters
    For Each sItem In .SlicerItems
        If sItem.Value >= 1 And sItem.Value <= 1.5 Then
            sItem.Selected = True
        Else
            sItem.Selected = False
        End If
    Next
End With

pvt.ManualUpdate = False
Set pvt = Nothing
End Sub

 

[Lars Ole Sørensen]

Hi Chihiro,

Thank you very much. This is exactly what I wanted.

Kindly Regards
Lars Ole