VBA loop through slicer and export multiple sheets to PDF

[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 Run-time error '5': Invalid procedure call or argument

And the code highlighted in yellow when Debug is

SC.VisibleSlicerItemsList = SL.SlicerItems(SlicerverdiIndex + 1).Name

Below is my full code. Any help is hugely appreciated!

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

 

[John_w]

Try this macro:

Public Sub Create_PDFs_From_Slicer_Items()

    Dim folderPath As String
    Dim currentSheet As Worksheet
    Dim slCache As SlicerCache
    Dim slItem As SlicerItem, slMatch As SlicerItem
    
    'Define folder path for output PDFs
    
    folderPath = "C:\Users\folder\"    'CHANGE THIS
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    Set currentSheet = ActiveSheet
    
    Set slCache = ThisWorkbook.SlicerCaches("Slicer_Product_Name2")
    
    Application.ScreenUpdating = False
    
    'Loop through each slicer item
    
    For Each slItem In slCache.SlicerItems
        
        'Show all items
        
        slCache.ClearManualFilter
        
        If slItem.HasData Then
                        
            'Select the next slicer item to update the chart sheets with that item
            
            For Each slMatch In slCache.SlicerItems
                If slItem.Name = slMatch.Name Then slMatch.Selected = True Else: slMatch.Selected = False
            Next
            
            'Save multiple sheets as a PDF
            
            ThisWorkbook.Sheets(Array("Sales", "Demand", "Supplier", "Inventory", "Distributor")).Select
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                            Filename:=folderPath & slItem.SourceName & ".pdf", _
                                            Quality:=xlQualityStandard, _
                                            IncludeDocProperties:=True, _
                                            ignorePrintAreas:=False, _
                                            OpenAfterPublish:=False
    
            currentSheet.Select
        
        End If
        
    Next
    
    slCache.ClearManualFilter
        
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub

 

[Helena123]

Thank you very much for your help! However, I encountered an error as below for the

 For Each slItem In slCache.SlicerItems

Do you know how this can be resolved?

FYI, all the pivot charts, slicers and measures are added in the data model in Power Pivot.

 

[John_w]

The error is probably caused by this line specifying the wrong slicer name:

Set slCache = ThisWorkbook.SlicerCaches("Slicer_Product_Name2")

where I used the same slicer name as your code, despite you saying the slicer is named "Product Name". Look at the Slicer Settings to determine the correct name (the 'Name to use in formulas').

Or you can refer to a slicer by its index number, so if it's the first slicer:

Set slCache = ThisWorkbook.SlicerCaches(1)

 

[Helena123]

Thanks for your quick response!

I have tried with

    Set slCache = ThisWorkbook.SlicerCaches("Slicer_Product_Name2")
    Set SL = slCache.SlicerCacheLevels(1)
    Set slItem = SL.SlicerItems(1)

and I got the run-time error 1004 again with

slMatch.Selected = True

Could you please help?

 

[John_w]

Could you upload a copy of your workbook to a file sharing site (e.g. OneDrive, Google Drive) which reproduces the problem, with sensitive data removed, and post the link here.

 

[Helena123]

Could you upload a copy of your workbook to a file sharing site (e.g. OneDrive, Google Drive) which reproduces the problem, with sensitive data removed, and post the link here.

Please see the link :https://docs.google.com/spreadsheet...ouid=113904769561505075060&rtpof=true&sd=true

There are 2 modules, Test_1 is my original one, Test_2_help is from your help

Thank you very much!

 

[Helena123]

FYI, you might find the sample excel file is different from the initial description because the real file has confidential information, so I was trying to make up an example about the issue.

 

[John_w]

The link doesn't give 'anyone with the link' access so I can't download the file.

 

[Helena123]

Hi, Could you please try opening the link again?
Sample.xlsm

I have the below for the file.