Create multiple slicers for pivot chart *simplify* [VBA]

[Martin_H]

Hi,

I am using this part of code (see below) to create multiple slicers (only 2 are shown here) for my pivot chart.

I have been wondering if it is possible to simplify/merge the code.

Thank you for help.

Dim SLcache As SlicerCache
Dim SL As SLICER
Dim wb As Workbook
Set wb = ActiveWorkbook

'Slicer Date
Set SLcache = wb.SlicerCaches.Add2(pt, "Date", "Date_May")

Set SL = SLcache.SLICERS.Add(ws, , "Date_May", "Date", 0, 0, 100, 150)

With SL
.Top = 30: .Left = 100: .Style = "SlicerStyleLight4": .RowHeight = 15: .ColumnWidth = 80
End With


'Slicer Machine
Set SLcache = wb.SlicerCaches.Add2(pt, "Machine", "Machine_May")

Set SL = SLcache.SLICERS.Add(ws, , "Machine_May", "Machine", 0, 0, 100, 150)

With SL
.Top = 30: .Left = 216: .Style = "SlicerStyleLight1": .RowHeight = 15: .ColumnWidth = 80
End With

 

[Domenic]

Maybe something like this...

Option Explicit

Sub test()

    Dim wb As Workbook
    Set wb = ActiveWorkbook
    
    Dim ws As Worksheet
    Set ws = wb.ActiveSheet
    
    Dim pt As PivotTable
    Set pt = ws.PivotTables("PivotTable1")
    
    Dim fieldNames As Variant
    fieldNames = Array("Date", "Machine")
    
    Dim slicerStyles As Variant
    slicerStyles = Array("SlicerStyleLight4", "SlicerStyleLight1")
    
    Dim nextLeftPos As Long
    Dim gap As Long
    Dim i As Long
    
    nextLeftPos = 100 'starting left position
    gap = 20 'gap between slicers
    For i = LBound(fieldNames) To UBound(fieldNames)
        
        Dim SLcache As SlicerCache
        Set SLcache = wb.SlicerCaches.Add2(pt, fieldNames(i), fieldNames(i) & "_May")
        
        Dim SL As Slicer
        Set SL = SLcache.Slicers.Add(ws, , fieldNames(i) & "_May", fieldNames(i), 0, 0, 100, 150)
        
        With SL
            .Left = nextLeftPos
            .Top = 30
            .Style = slicerStyles(i)
            .RowHeight = 15
            .ColumnWidth = 80
            nextLeftPos = nextLeftPos + .Width + gap
        End With
        
    Next i

End Sub

Hope this helps!