excelvbaslicers

Adding Slicers to Pivot Table and aligning them in specified order

I have the code that will create 4 slicers based on the assigned pivot Table. The slicers are to be placed in the same equal distance from each other starting with Cell A2. When I run the code I am getting a Runtime Error and message that Method Add2 with object slicercaches failed. I am not sure on how to fix it.

Sub AddSlicers()
Dim ws As Worksheet
Dim pt As pivotTable
Dim sl1 As slicerCache, sl2 As slicerCache, sl3 As slicerCache, sl4 As slicerCache
Dim sl1Obj As slicer, sl2Obj As slicer, sl3Obj As slicer, sl4Obj As slicer

' Set the worksheet and pivot table
On Error Resume Next
Set ws = ActiveWorkbook.Sheets("sanity_check")
On Error GoTo 0

If ws Is Nothing Then
    MsgBox "Worksheet 'sanity_check' not found!", vbExclamation
    Exit Sub
End If

On Error Resume Next
Set pt = ws.PivotTables("Origin_Check")
On Error GoTo 0

If pt Is Nothing Then
    MsgBox "Pivot table 'Origin_Check' not found in the 'sanity_check' worksheet!", vbExclamation
    Exit Sub
End If

' Add slicer caches
Set sl1 = ActiveWorkbook.slicercaches.Add2(pt, "Origin_Region")
Set sl2 = ActiveWorkbook.slicercaches.Add2(pt, "Origin_Country")
Set sl3 = ActiveWorkbook.slicercaches.Add2(pt, "Destination_Region")
Set sl4 = ActiveWorkbook.slicercaches.Add2(pt, "Destination_Country")

' Add slicers
Set sl1Obj = sl1.Slicers.Add(ws, , "Slicer1", "Origin_Region" & Chr(10) & "(enter AP, AM, EURO, MEA)", _
    Left:=ws.Range("A2").Left, Top:=ws.Range("A2").Top)
Set sl2Obj = sl2.Slicers.Add(ws, , "Slicer2", "Origin_Country" & Chr(10) & "(in 2-letter codes)", _
    Left:=ws.Range("B2").Left, Top:=ws.Range("B2").Top)
Set sl3Obj = sl3.Slicers.Add(ws, , "Slicer3", "Destination_Region" & Chr(10) & "(enter AP, AM, EURO, MEA)", _
    Left:=ws.Range("C2").Left, Top:=ws.Range("C2").Top)
Set sl4Obj = sl4.Slicers.Add(ws, , "Slicer4", "Destination_Country" & Chr(10) & "(in 2-letter codes)", _
    Left:=ws.Range("D2").Left, Top:=ws.Range("D2").Top)

' Refresh the pivot table
pt.RefreshTable
End Sub
Solution

  • Macro recording is great for learning syntax. The second parameter of SlicerCaches.Add2 and third of Slicers.Add need fixing. See revised code for corrections.

        ' recording macro
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("Origin_Check"), _
        "Origin_" & Chr(10) & "Region" & Chr(10) & "(enter AP, AM, EURO, MEA)").Slicers.Add ActiveSheet, , _
        "Origin_" & Chr(10) & "Region" & Chr(10) & "(enter AP, AM, EURO, MEA)", _
        "Origin_" & Chr(10) & "Region" & Chr(10) & "(enter AP, AM, EURO, MEA)", 87, 285, 144, 194.25
    
    ' Your code from OP
    Set sl1 = ActiveWorkbook.SlicerCaches.Add2(pt, "Origin_Region")
    Set sl1Obj = sl1.Slicers.Add(ws, , "Slicer1", "Origin_Region" & Chr(10) & "(enter AP, AM, EURO, MEA)", _
        Left:=ws.Range("A2").Left, Top:=ws.Range("A2").Top)
    
    ' Revised code
    Dim sField As String
    sField = "Origin_" & Chr(10) & "Region" & Chr(10) & "(enter AP, AM, EURO, MEA)"
    Set sl1 = ActiveWorkbook.SlicerCaches.Add2(pt, sField)
    Set sl1Obj = sl1.Slicers.Add(ws, , "Slicer1", sField, _
        Left:=ws.Range("A2").Left, Top:=ws.Range("A2").Top)