VBA to filter through slicer options and save pdfs

[Otawata]

Hi,

I followed a tutorial online- https://www.mrexcel.com/excel-tips/vba-all-slicer-combinations/

I have 4 items within one main slicer and would like to loop through and save each as a separate pdf file.

I'm able to generate the range, "SlicerItems1" accurately. I'm also able to generate 4 pdf files, total, at the end but they are for the original item in the slicer, only. It seems like the loop is occurring, but that it isn't updating the item selected in the slicer. Please provide any thoughts if you can and thank you!

VBA All Slicer Combinations

Regular pivot table filters offer the Show All Report Filter pages, but Slicers do not support this functionality. Today, some VBA to loop through all possible slicer combinations.

www.mrexcel.com

>>> use code - tags <<<

Sub DoAllCombinations()
    Dim sc1 As SlicerCache
    Dim sI As SlicerItem
    Dim cell1 As Range
 
    Set sc1 = ThisWorkbook.SlicerCaches("Slicer_ClientName")
    sc1.ClearAllFilters
    Ctr = 1
 
    NextCol = ActiveSheet.UsedRange.Columns.Count + 2
    RememberCol = NextCol
    NextRow = 1
    For Each sI In sc1.SlicerItems
        Cells(NextRow, NextCol).Value = sI.Caption
        NextRow = NextRow + 1
    Next sI
    LastRow = NextRow - 1
    Cells(1, NextCol).Resize(LastRow, 1).Name = "SlicerItems1"
    NextRow = 1
    NextCol = NextCol + 1

    For Each cell1 In Range("SlicerItems1")
        sc1.ClearAllFilters
        For Each sI In sc1.SlicerItems
            If sI.Caption = cell1.Value Then
                sI.Selected = True
            Else
                sI.Selected = False
            End If

        Next sI

            MyFilename = "C:\" & Format(Ctr, "000") & ".pdf"
            On Error Resume Next
            Kill (MyFilename)
            On Error GoTo 0
            Sheets("Summary").Range("A8:F135").ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFilename, Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
             Ctr = Ctr + 1

    Next cell1
    Cells(1, RememberCol).Resize(1, 3).EntireColumn.Clear
    MsgBox (Ctr - 1) & " reports have been created!"
 
End Sub

 

[Otawata]

Ah, I uncovered the issue was with my slicer name. Soooo silly.