Lop through slicer until end

**3345james** · 12-19-2015, 11:24 AM

How can I loop through a slicer called Provider on a WS called Provider Names

The code below is what I use to execute each selection.
(Code modified by Jerry Beaucaires - Thanks Jerry)

Option Explicit

Sub Save_As_PDF2()
Application.ScreenUpdating = False

Dim savePATH As String, saveName As String

savePATH = "C:\Users\Jim\Documents\!LGTC\Network\" & Format(Date, "MM-DD-YYYY") & "\"
saveName = ActiveSheet.Range("c2").Text & ".pdf"

On Error Resume Next
MkDir savePATH
On Error GoTo 0

Sheets(Array("Letter", "Veh License Exp", "Employee Exp", "Insurance Exp")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePATH & saveName, _
     Quality:=xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=False, OpenAfterPublish:=False

Sheets("Provider Names").Select
      Range("C7").Select
      
Application.ScreenUpdating = True
End Sub

**AlphaFrog** · 12-19-2015, 06:09 PM

This should loop through the slicer items and call your PDF macro for each.

Sub SlicerTest()
    
    Dim sli As SlicerItem
    
    For Each sli In ActiveWorkbook.SlicerCaches("Provider").SlicerCacheLevels(1).SlicerItems
        sli.VisibleSlicerItemsList = Array(sli.Name)
        Call Save_As_PDF2
    Next sli
    
End Sub

Accessing the Slicer through VBA

**3345james** · 12-19-2015, 06:48 PM

Thanks AlphaFrog

Run time error 1004
Invalid Procedure Call or Argument
@For Each sli In ActiveWorkboo. . .

**AlphaFrog** · 12-19-2015, 07:26 PM

Originally Posted by 3345james

Thanks AlphaFrog

Run time error 1004
Invalid Procedure Call or Argument
@For Each sli In ActiveWorkboo. . .

Look at the settings for the slicer (right click on the slicer and select slicer settings). Use the Name to use in Formulas name.

slicer12.gif

The slicer name

**3345james** · 12-19-2015, 08:12 PM

Same issue - settings for formula = "Slicer_Provider"

**AlphaFrog** · 12-19-2015, 09:28 PM

Give this s try.

Sub SlicerTest()
    
    Dim sli As SlicerItem
    
    With ActiveWorkbook.SlicerCaches("Slicer_Provider")
        For Each sli In .SlicerItems
            .VisibleSlicerItemsList = Array(sli.Name)
            Call Save_As_PDF2
        Next sli
    End With
    
End Sub

**3345james** · 12-19-2015, 09:35 PM

still same error
@.VisibleSlicerItemsList = Array(sli.Name)

Lop through slicer until end

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Lop through slicer until end

Re: Lop through slicer until end

Re: Lop through slicer until end

Re: Lop through slicer until end

Re: Lop through slicer until end

Re: Lop through slicer until end

Re: Lop through slicer until end

Thread Information

Users Browsing this Thread

Similar Threads

Can I Sync a Slicer to Another Slicer If....

How to protect slicer

Make one slicer mirror filters of another slicer

how use slicer

Slicer Macro - unfilter another slicer on selection

[SOLVED] Change Chart Title Depending on a Slicer - slicer advice

Slicer Help

Bookmarks

Bookmarks

Posting Permissions