Loop through pivot slicer and select values

Question

I have a pivot slicer with a list of sites that are linked to a PivotTable. I want to loop through the slicer to achieve the following:

selects a site
displays the relevant values
exports the data to a PDF file
select next site etc

I've got the code that exports the data to a PDF file working but I'm struggling to loop through the slicer and filter on each site. At the moment I have the following:

Sub ExportPDFs()

Dim sI As SlicerItem

For Each sI In ActiveWorkbook.SlicerCaches("Slicer_site").SlicerItems

    Debug.Print sI.Name
    Debug.Print Range("A3")

    '    ChDir "Q:\PROMs\Data Completeness\PDFs"

    '    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    '        "Q:\PROMs\Data Completeness\PDFs\PROMS data completeness - " & sI.Name & ".pdf", _
    '        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
     '        :=False, OpenAfterPublish:=False

Next

End Sub

I've added the debug.print lines (cell "A3" contains the site name on my worksheet, so this changes when I select a different slicer item) to test it, it loops through each slicer item but doesn't change the data so the debug window shows the following:

Site 1
Site 1
Site 2
Site 1
Site 3
Site 1
Site 4
Site 1

How do I make it filter the data each time it loops through?

Answer 1

You need to actively tell VBA to select the specific slicer item and deselect the rest. There may be an even more efficient way to do this, but I just created and tested the following and it worked:

Dim sI As SlicerItem, sI2 As SlicerItem, sC As SlicerCache

Set sC = ActiveWorkbook.SlicerCaches("Slicer_site")

With sC

    For Each sI In sC.SlicerItems

        sC.ClearManualFilter

        For Each sI2 In sC.SlicerItems
            If sI.Name = sI2.Name Then sI2.Selected = True Else: sI2.Selected = False
        Next

        Debug.Print sI.Name
        'add export to PDF code here

    Next

End With