Showing results for 
Search instead for 
Did you mean: 
datasetleo Frequent Visitor
Frequent Visitor

Excel - Power Query: VBA for looping through slicers created using data model. Help!

All - this is in RE: Excel VBA. I am looking for looping through slicer selection and print PDF's. my code does work for standard pivots, but it does not work when using it on PowerPivot which is what I am looking for.

Can anyone please shed some light on how to turn the below so it works in powerpivot? (slicer from power pivot source data)


Let me know any questions - looking forward to hearing any tips Smiley Happy Smiley Happy


Thanks! Smiley Happy





Option Explicit

Sub ExportPDFs()

Dim sI As SlicerItem, sI2 As SlicerItem, sc As SlicerCacheDim fname$

Set sc = ActiveWorkbook.SlicerCaches("Slicer_Date")

With sc

    For Each sI In sc.SlicerItems
        If sI.HasData = True Then        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
        fname = sI.Caption & " " & Format(Date, "MM-DD-YYYY") & " " & "Report"        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fname, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End If
    Next    ActiveWorkbook.SlicerCaches("Slicer_Year").ClearManualFilter
    MsgBox "Reports Saved"
End With
End Sub
Community Support Team
Community Support Team

Re: Excel - Power Query: VBA for looping through slicers created using data model. Help!

Hi @datasetleo 

As tested, i'm afriad it doesn't support to loop slicer and export to pdf programmatically currently.

There is a custom visual which works like a dynamic slicer and animates your other power bi visuals without any user interaction.


Best Regards


Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.