VBA code to loop through all options within a single slicer and print individual reports for each

[lorbieckit]

I have a single pivot table slicer that contains 39 different last names. When I click on a last name it populates a dashboard with that athletes testing metrics. My goal is to create a VBA code that will loop through all 39 athletes and print individual reports for each to my default printer in a single action. I want to be able to attach this code to a print button on the dashboard.

I know this should be relatively easy as I can find bits and pieces of code to support this but am struggling to put it all together. Any help is appreciated!

 

[John_w]

Try this macro - assign it to your print button.

Public Sub Print_Report_for_each_Slicer_Item()

    Dim slCache As SlicerCache
    Dim slItem As SlicerItem, slMatch As SlicerItem
    
    Set slCache = ThisWorkbook.SlicerCaches(1)
    
    'Select each slicer item and print active sheet
    
    For Each slItem In slCache.SlicerItems
        slCache.ClearManualFilter
        For Each slMatch In slCache.SlicerItems
            If slItem.Name = slMatch.Name Then slMatch.Selected = True Else: slMatch.Selected = False
        Next
        ActiveSheet.PrintOut
    Next
    
    slCache.ClearManualFilter
            
    MsgBox "Done"
    
End Sub