Slicer

[deadeye123]

I have a slicer that has multiple values. the slicer controls multiple pivot tables. I need to select each value in the slicer and print a PDF. there are 100 values in the slicers and do it manually is a time consuming. how do I automate the process via macros or modules?

 

[deadeye123]

Any recommendation? is there a VBA or macro that does that?

 

[Domenic]

Try the following macro...

Assumptions:

1) The workbook containing the slicer and pivot tables is the active workbook.

2) The worksheet containing your pivot tables is the active sheet.

Notes:

1) Change the name of the slicer where indicated in the code.

2) Change the path to the destination folder where indicated in the code.

3)  Each PDF file is named based on its respective slicer item name (ie. SlicerItemName.pdf).

4)  If a file by the same name already exists, it will be overwritten.

Here's the macro...

Option Explicit

Sub CreatePDFForEachSlicerItem()

    Const sSlicerName As String = "Region" [COLOR=#008000]'change the slicer name accordingly[/COLOR]
    
    Dim sDestFolder As String
    Dim Idx As Long

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    On Error GoTo ErrHandler
    
    sDestFolder = "C:\Users\Domenic\Desktop\" [COLOR=#008000]'change the path accordingly[/COLOR]
    If Len(Dir(sDestFolder, vbDirectory)) = 0 Then
        MsgBox sDestFolder & " does not exist.", vbInformation
        GoTo ExitTheSub
    End If
    
    If Right(sDestFolder, 1) <> "\" Then
        sDestFolder = sDestFolder & "\"
    End If

    With ActiveWorkbook.SlicerCaches("Slicer_" & sSlicerName)
        .ClearManualFilter
        With .SlicerItems
            For Idx = 1 To .Count
                If Idx > 1 Then
                    .Item(Idx).Selected = False
                End If
            Next Idx
            For Idx = 1 To .Count
                ActiveSheet.ExportAsFixedFormat xlTypePDF, sDestFolder & .Item(Idx).Caption & ".pdf"
                If Idx < .Count Then
                    .Item(Idx + 1).Selected = True
                    .Item(Idx).Selected = False
                End If
            Next Idx
        End With
        .ClearManualFilter
    End With
    
    MsgBox "Completed...", vbInformation
    
ExitTheSub:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & ":  " & Err.Description, vbCritical, "Error"
    Resume ExitTheSub
    
End Sub

Hope this helps!

 

[excelwithclark]

Thanks Domenic.
What if i have multiple slicers?

 

[IrishChristof]

Hi Dominic,
This is amazing.
Is it possible to make it so that it doesnt print Slicer values where there is no data?

 

[IrishChristof]

Does this work if the pivot tables are build using a DataModel and PowerPivot?