Loop through slicer and save to PDF

vedderman

New Member
Joined
Nov 2, 2016
Messages
27
This has been asked here and other sites, but for the life of me I can't figure out how to get it to work. I have a workbook built in Powerpivot. First sheet is a bunch of slicers for province, city, area, region, division. I want to loop through the divisions and save the second and third sheets in my book as a PDF. Code below seems simple enough, but errors at the Each sI In sC.SlicerItem part.

Code:
Sub PrintToPDF()

Dim sI As SlicerItem, sI2 As SlicerItem, sC As SlicerCache


Set sC = ActiveWorkbook.SlicerCaches("Slicer_Profit_Centre_Level_8_Descripton") 'not a spelling error'


With sC


    For Each sI In sC.SlicerItem


        sC.ClearManualFilter


        For Each sI2 In sC.SlicerItems
            If sI.Name = sI2.Name Then sI2.Selected = True Else: sI2.Selected = False
        Next
        
        Dim FName           As String
        Dim FPath           As String
     
    FPath = "I:\Accounting\Data\Margin Delta\"
    FName = "&sI.Name"
        
       ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "FPath & " \ " & FName & .pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
   Next
   End With
   


End Sub

Any help would be appreciated. Tried recording the steps but it didn't really help me decipher my issue.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,177
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top