VBA Macro for Looping Through Slicer and Saving as PDF

[cedman02]

Hi All,

I am trying to loop through each option in my Slicer and save a pdf version of the dashboard which populates to show all the rep data which I then want to save to the selected file path But I keep on ending up with the error Object required. Is there something that I am forgetting to add in as I am still trying to learn VBA coding

Sub myMacro()
Dim sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Full_Name")


'This reminds the user to only select the first slicer item
If sC.VisibleSlicerItems.Count <> 1 Or sC.SlicerItems(1).Selected = False Then
MsgBox "Please Only Select Rep Number 1"
Exit Sub
End If




For i = 1 To sC.SlicerItems.Count


'Do not clear filter as it causes to select all of the items (sC.ClearManualFilter)


sC.SlicerItems(i).Selected = True
If i <> 1 Then sC.SlicerItems(i - 1).Selected = False




'Debug.Print sI.Name
'add export to PDF code here
With Sheet2.PageSetup


.PrintArea = Break_Down.Range("A1:X295" & lastRow).Address


.FitToPagesWide = 1
.FitToPagesTall = 1


End With


Break_Down.Range("B2") = sC.SlicerItems(i).Name


'This prints to C directory, change the path as you wish

Sheet2.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"L:\XYZ\Sales Ops\K\NEW" & Range("B2").Text & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next


End Sub

 

[cedman02]

thank you for the help i advance with anything that can be helpful