Select single slicer item from Table VBA

Question

I am trying to print off reports for selected budget holders (selected from a Budget Holder Table), using the budget holder name to feed into the slicer which then updates various pivot tables. The issue is the code selects all the budget holders in the slicer rather than taking the single selected budget holder I am picking up from the table.

Sub PrintPDFsSO()

    Dim Lobj As ListObject
    Dim Budholder As String
    Dim Path As String
    Dim x As Long, y As Long, Number_of_rows As Long
    Dim SourceBk As Workbook
    Dim SlicItem As SlicerItem, SlicDummy As SlicerItem, SlicCache As SlicerCache
    Dim pt As PivotTable, wb As Workbook, ws As Worksheet

    Set SourceBk = ThisWorkbook
    Set Lobj = SourceBk.Sheets("BudHolders").ListObjects("BudHolderList")
    Set SlicCache = SourceBk.SlicerCaches("Slicer_Budget_Holder")

    For x = 1 To Lobj.DataBodyRange.Rows.Count   'Budget Holders held in    BudHolderList Table

        Dim BudHolders()
        ReDim BudHolders(1 To Lobj.DataBodyRange.Rows.Count) 'as Budholders will only ever hold one budget hodler name, can this be simpified?
        Dim Counter As Long

        Counter = 1

        If Not Lobj.DataBodyRange.Rows(x).EntireRow.Hidden Then

            Budholder = Lobj.DataBodyRange(x, 3) 'Name of budget holder held in 3rd column of Budget Holder Table

            BudHolders(Counter) = Budholder      'Budholders holds the budget holder name

            Counter = Counter + 1

            ReDim Preserve BudHolders(1 To Counter - 1)

            ' Trying to stop slicers/pivot tables calculating so code setting new filter on budget name doesnt get stuck - but not working
            Application.Calculation = xlCalculationManual

            For Each ws In SourceBk.Sheets

                For Each pt In ws.PivotTables

                    pt.ManualUpdate = True

                Next pt

            Next ws

            'Code to change budget holder in slicer to next budget holder in selection from Table
            For y = LBound(BudHolders) To UBound(BudHolders)

                With SlicCache

                    .ClearManualFilter           'clears all filters and shows all items in budget holder slicer

                    For Each SlicItem In .SlicerItems

                        If BudHolders(y) <> SlicItem.Value Then 'Tests if the slicer item matches the current a value of budholder

                            SlicItem.Selected = False 'Grinding to a virtual halt on this line as it 'calculates and populates pivot table report'

                        End If

                    Next SlicItem

                End With

            Next y

            Application.Calculation = xlCalculationAutomatic

            For Each ws In SourceBk.Sheets

                For Each pt In ws.PivotTables

                    pt.ManualUpdate = False

                Next pt

            Next ws

            'Use budholder name which will populate some graphs etc in workbook with new figures
            SourceBk.Sheets("Graphs - Summary").Range("BudHolder_SG").Value = Budholder

            'Do Printing, saving etc
        End If

    Next

End Sub

Answer 1

I have found a workaround by working with one of the pivot tables rather than with the slicer. Because the tables are all connected (i.e. all have the budget holder as a filter field and are connected via the slicer), when I the budget holder is updated in the PivotField in the Pivot Table, it will update all the either pivot tables with the same PivotField value.

So the code to replace the slicer code in original problem is simply:

With sheets ("BudgetHolder").PivotTables("PivotTable1").PivotFields("BudgetHolder")
.ClearAllFilters
.CurrentPage=Budholder
End With