+ Reply to Thread
Results 1 to 2 of 2

VBA loop through slicer and export multiple sheets to PDF

Hybrid View

  1. #1
    Registered User
    Join Date
    12-01-2021
    Location
    Amsterdam
    MS-Off Ver
    365
    Posts
    1

    Question VBA loop through slicer and export multiple sheets to PDF

    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
    Last edited by Helena123; 12-02-2021 at 04:39 AM.

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,765

    Re: VBA loop through slicer and export multiple sheets to PDF

    Please read Forum Rules point 3.

    Cross-post here: https://www.mrexcel.com/board/thread...o-pdf.1189203/
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 08-16-2020, 12:40 AM
  2. VBA to loop through slicer option and export each to PDF
    By KirstieA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-16-2019, 05:55 PM
  3. Loop through slicer, but don't do slicer action when values are unselected
    By as_sass in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-08-2018, 04:55 PM
  4. Loop through slicer, while also selecting single slicer value on separate slicer
    By as_sass in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-30-2018, 01:41 PM
  5. query with filter or slicer to generate live multiple sheets
    By phpolicylady in forum Excel General
    Replies: 3
    Last Post: 03-25-2018, 01:15 PM
  6. Export Excel sheets and send them as emails (Loop)
    By alexander.gavrailov in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-08-2017, 09:09 AM
  7. Export multiple object to multiple sheets in Excel
    By tomasevicstefan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-09-2014, 01:21 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1