• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Export multiple sheets to PDF per product by looping through slicer

Helena123

New Member
I have an excel file containing multiple charts showing sales, demand, etc. in 5 sheets. These charts are linked and filtered by a slicer "Product Name". This slicer is located in sheet "Supplier" .
I am trying to export these 5 sheets to PDF per product by having the code to loop through slicer. The error I got is



And the code highlighted in yellow when Debug is
Code:
SC.VisibleSlicerItemsList = SL.SlicerItems(SlicerverdiIndex + 1).Name
Below is my full code. I have spent weeks to research and try different suggestions on internet but no luck. Could anyone please help?

Code:
Sub ExportPDF()Dim SC As SlicerCache
Dim SL As SlicerCacheLevel
Dim SI As SlicerItem
Dim PrintRange As Range
Dim MyRangeArray As Variant
Dim i As Integer
Dim FName As String
Dim FPath As String


  Set sC = ActiveWorkbook.SlicerCaches("Slicer_Product_Name2") 'Add slicer name between " "
  Set SL = sC.SlicerCacheLevels(1)
  Set sI = SL.SlicerItems(1)        'Sets slicer item to a start value


  'c(ounter) is set to 1, ready to begin
  c = 1


'Repeat the loop until the slicer doesnt have any data. "Do while c=1" is to kick it off in the first place
   Do While c = 1 Or sI.HasData = True


'This makes sure that SI is the correct slicer. Needed for corrent file name.
    For Each SI In SL.SlicerItems
        If SI.Selected = True Then
        SlicerverdiIndex = c
    Exit For
        End If
    Next SI



'Ensure that print only happens when the slicer has data
If sI.HasData = True Then




    'Define file path for printed file storage
    FPath = "C:\Users\..." 
    FName = SI.SourceName


    'Define WHAT to print and how to build file name
    'List of Excel Ranges to export from'


    ThisWorkbook.Sheets(Array("Sales", "Demand", "Supplier", "Inventory", "Distributor")).Select
                  


    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                        Filename:=FPath & "\" & FName & ".pdf", _
                                        Quality:=xlQualityStandard, _
                                        IncludeDocProperties:=True, _
                                        IgnorePrintAreas:=False, _
                                        OpenAfterPublish:=False


    'PRINT CODE FINISHED
End If


'Select next Value in slicer
SC.VisibleSlicerItemsList = SL.SlicerItems(SlicerverdiIndex + 1).Name


'Adds 1 to the counter, will loop until end of slicer has been reached.
c = c + 1


Loop


End Sub
 
Top