VBA code to loop through a slicer printing each slicer item

[ffarah]

I have been trying to write a VBA code to loop through a slicer but keep getting an error "1004 Application-defined or object-defined error". I can get the print to PDF working, but unable to loop. below is my code. Appreciate any help.. thanks.

Sub Step_Thru_SlicerItems2()
Dim slItem As SlicerItem
Dim i As Long

Application.ScreenUpdating = False
With ActiveWorkbook.SlicerCaches("Slicer_Shareholder_Name2")
'--deselect all items except the first
.SlicerItems(1).Selected = True
For Each slItem In .VisibleSlicerItems
If slItem.Name <> .SlicerItems(1).Name Then _
slItem.Selected = False
Next slItem
Call Saveaspdf
'--step through each item and run custom function
For i = 2 To .SlicerItems.Count
.SlicerItems(i).Selected = True
.SlicerItems(i - 1).Selected = False
Call Saveaspdf
Next i
End With
Application.ScreenUpdating = True
End Sub

 

[Monty]

ffarah

Welcome to the forum..

Can you please upload the file to help you.

Thanks!

 

[ffarah]

ffarah

Welcome to the forum..

Can you please upload the file to help you.

Thanks!

Thanks. See attached.
Just trying to make a simple loop of the slicer items and printing a file at each item, but keep getting that error. I have tried a number of different code but none seem to work. Really appreciate your help.
Thanks

Frank

 

[Monty]

Hi
Try thisbasic loop through slicer items

Sub Test()
    Dim slItem As SlicerItem
    For Each slItem In ActiveWorkbook _
        .SlicerCaches("Slicer_Network_Status").SlicerItems
            MsgBox "SlicerItem Name: " & slItem.Name _
                & vbCr & "Selected= " & slItem.Selected
    Next
End Sub

 

[ffarah]

Hi Monty
I ran the script as requested but still ended up with the same "1004 Application-defined or object-defined error". at "For Each slItem In ActiveWorkbook _
.SlicerCaches("Slicer_Shareholder").SlicerItems"
The only thing changed was the slicer name from ("Slicer_Network_Status") to ("Slicer_Shareholder")

Sub Test()
  Dim slItem As SlicerItem
  For Each slItem In ActiveWorkbook _
  .SlicerCaches("Slicer_Shareholder").SlicerItems
  MsgBox "SlicerItem Name: " & slItem.Name _
  & vbCr & "Selected= " & slItem.Selected
  Next
End Sub