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
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