Export multiple sheets to PDF per product by looping through slicer

[Helena123]

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

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?

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

[georgiboy]

Hi Helena,

I had an issue like this before, my solution was not pretty but I will share it anyway, I have amended your code to work on my dummy workbook and added a function for it to use:

Code:

Sub ExportPDF()    
    Dim sC As SlicerCache
    Dim x As Long
    Dim FName As String
    Dim FPath As String
    
    Set sC = ActiveWorkbook.SlicerCaches("Slicer_Product")
    
    For x = 1 To sC.SlicerItems.Count
        SlicerSelect "Slicer_Product", sC.SlicerItems(x).Name
        FPath = "C:\Users\..."
        FName = sC.SlicerItems(x).Name
        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
    Next x
    
End Sub

Function:

Function SlicerSelect(SlicerName As String, SlicerItem As String)    
    Dim SC As SlicerCache
    Dim SI As SlicerItem
    Dim index As Integer
    
    Set SC = ActiveWorkbook.SlicerCaches(SlicerName)
    SC.ClearManualFilter
    For Each SI In SC.SlicerItems
        If SI.Name = SlicerItem Then
            SI.Selected = True
        Else
            SI.Selected = False
        End If
    Next SI
End Function

Hope this helps or spawns another idea

[Helena123]

Hi georgiboy,


I have just applied your code and got an run-time error 1004: Application-denied or object-defined error

With line

For x = 1 To SC.SlicerItems.Count

[georgiboy]

You may need to incorporate the level you had before:

Set SL = sC.SlicerCacheLevels(1)

and play with it, from my understanding the way you work with slicers depends on the way the data is created in the first place.

You may be able to include the function provided within your own code.

Hope you find a solution soon

[Paul_Hossler]

I don't think you want to use VisibleSlicerItemsList

https://docs.microsoft.com/en-us/dot...view=excel-pia

The VisibleSlicerItemsList property is only applicable for slicers that are based on OLAP data sources (SlicerCache.OLAP = true).

Try integrating something like this

Option Explicit


Sub test()
    Dim oSlicerCache As SlicerCache
    Dim oSlicer As Slicer
    Dim oSlicerItem As SlicerItem
    
    Set oSlicerCache = ActiveWorkbook.SlicerCaches("Slicer_AAA")
    Set oSlicer = oSlicerCache.Slicers("AAA")
    
    oSlicerCache.ClearManualFilter


    For Each oSlicerItem In oSlicer.SlicerCache.SlicerItems
        With oSlicerItem
            If .Selected Then
                MsgBox .Value
            End If
        End With
    Next


End Sub

[Helena123]

I have applied that and it works for me, thank you.

However, now the chart format keep changing, some products still have custom format when exported to PDF, some turns back to default format. And it does happen when I manually select item in the slicer.