VBA loop through slicer and export multiple sheets to PDF (withoud hidden slicer items)

[tmoreira001]

Hi, I am using the below VBA to export to pdf based on a slicer. The main problem is that it is selecting those slicers that are not visible (no data). How can I have a loop with only visible slicers? HELP PLEASE!

Sub Reporte()
Dim sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches("Slicer_X")

For i = 1 To sC.SlicerItems.Count

sC.SlicerItems(i).Selected = True
If i <> 1 Then sC.SlicerItems(i - 1).Selected = False
   
    ActiveSheet.Range("AA2") = sC.SlicerItems(i).Name

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\X\Desktop\" & "Reporte" & Range("AA2").Text & ".pdf", Quality:= _
        xlQualityHigh, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

Next


End Sub

 

[Domenic]

Assuming that your slicer is connected to a pivot table, try setting the MissingItemsLimit property of the PivotCache object to xlMissingItemsNone, and refreshing the pivot cache before looping through your slicer items, as follows . . .

Sub Reporte()
Dim sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches("Slicer_X")

With sC.PivotTables(1).PivotCache
    .MissingItemsLimit = xlMissingItemsNone
    .Refresh
End With

For i = 1 To sC.SlicerItems.Count

sC.SlicerItems(i).Selected = True
If i <> 1 Then sC.SlicerItems(i - 1).Selected = False
  
    ActiveSheet.Range("AA2") = sC.SlicerItems(i).Name

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\X\Desktop\" & "Reporte" & Range("AA2").Text & ".pdf", Quality:= _
        xlQualityHigh, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

Next


End Sub

Hope this helps!