How to add multiple slicers to same worksheet using VBA

[eric24g]

Hello,

I am trying to add multiple slicers to the worksheet, but my code is only adding either one or the other. Each slicer has a different pivot table coming from different worksheets within the workbook. How could I get it so that when the below code executes both slicers are added to the same worksheet.

Note: Pivot table for slicer 1 is coming from worksheet 1 and pivot table for slicer 2 is coming from worksheet 2.

---slicer 1 code------

Sub CreatSlicer1()
Dim rng As Range
Dim sl As Slicer
Dim sc As SlicerCache
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook

Set ws = Worksheets("Sheet2")
Set sourceSheet = Worksheets("worksheet 1")

On Error Resume Next
Workbooks("MyFile.xlsx").Worksheets("Sheet2").Activate
wb.SlicerCaches(1).Delete
On Error GoTo 0

Workbooks("MyFile.xlsx").Worksheets("worksheet 1").Activate
Set sc = wb.SlicerCaches.Add2(ActiveSheet.ListObjects("Table5"), "Category")
Set sl = sc.Slicers.Add(ws, , "Category 1", "Category")

Set rng = Range("W2:AC2")
sl.Top = rng.Top
sl.Left = rng.Left
sl.Width = 150 'there are 72 points to an inch or 28.35 points to a centimeter
sl.Height = 120

End Sub

---slicer 2 code----

Sub CreatSlicer2()
Dim rng As Range
Dim sl As Slicer
Dim sc As SlicerCache
Dim ws As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook

Set ws = Worksheets("Sheet2")
Set sourceSheet = Worksheets("worksheet 2")

On Error Resume Next
Workbooks("MyFile.xlsx").Worksheets("Sheet2").Activate
wb.SlicerCaches(1).Delete
On Error GoTo 0

Workbooks("MyFile.xlsx").Worksheets("worksheet 2").Activate
Set sc = wb.SlicerCaches.Add2(ActiveSheet.ListObjects("Table1"), "Category")

Set sl = sc.Slicers.Add(ws, , "Category 3", "Category")

Set rng = Range("F21:F21")
sl.Top = rng.Top
sl.Left = rng.Left
sl.Width = 160 'there are 72 points to an inch or 28.35 points to a centimeter
sl.Height = 120
Workbooks("MyFile.xlsx").Worksheets("Sheet2").Activate

End Sub

--I call this file below to execute both slicers----

Sub CreatAllChartsSlicers()
Call CreatSlicer1
Call CreatSlicer2
End Sub

 

[John_w]

It's not very clear which workbook and worksheet you're adding the slicers to. You seem to be adding the first slicer to the already open workbook "MyFile.xlsx" in the sheet "worksheet 1", so I've tried to do that in the following macro:

Public Sub Add_Slicer1()

    Dim wb As Workbook
    Dim sourceSheet As Worksheet
    Dim table As ListObject
    Dim sl As Slicer
    Dim sc As SlicerCache
    
    Set wb = Workbooks("MyFile.xlsx")
    Set sourceSheet = wb.Worksheets("worksheet 1")
    
    While wb.SlicerCaches.Count > 0
        wb.SlicerCaches(1).Delete
    Wend
    
    Set table = sourceSheet.ListObjects("Table5")
    Set sc = wb.SlicerCaches.Add2(table, "Category")
    Set sl = sc.Slicers.Add(sourceSheet, , "Category 1", "Category")
    
    With sourceSheet.Range("W2:AC2")
        sl.Top = .Top
        sl.Left = .Left
        sl.Width = 150
        sl.Height = 120
    End With
    
    wb.Save
    
End Sub

 

[eric24g]

Thank you. That worked. @John_w

 

[eric24g]

Hey John, I created another post where I was having issues with getting the slicers to work with my charts. If you have any time, could you look at it and see what I'm doing wrong. The link is below. Thanks. @John_w

Slicer not working on Chart in VBA, but works after selecting chart data manually

I created a VBA script that adds charts and slicers to my worksheet, but for some reason they don't work after adding them. Now, once the charts and slicers are added I would go and select on the chart, right click and click "select data" then click ok. Once I do that the slicers work with the...

www.mrexcel.com