Pivot Table Slicer [VBA]

[Martin_H]

Hello,

I have been using this code (look below) to create a Slicer for my Pivot Table, which is located at Worksheet_One.

The Silcer's name is "My_Slicer_Date"

It works well but I would like to use the same code to create the same Slicer but for another Pivot Table, which is basicaly same Pivot Table as the previous one, but with different data set and it is located at Worksheet_two.

So let's say I created Worksheet_One with Pivot Table and Slicer, all good here. Now I'm about to create Worksheet_Two with Pivot Table but I can't use the code for the Slicer unless I delete Worksheet_One.

Would it be possible to somehow use that code to create multiple Pivot Tables without having to delete any Worksheets and therefore Pivot Tables?

Thank you!

Sub create_slicer1()

Dim i As SlicerCaches
Dim j As SLICERS
Dim k As SLICER

Set i = ActiveWorkbook.SlicerCaches
Set j = i.Add(ActiveSheet.PivotTables(1), "Date", "My_Slicer_Date").SLICERS
Set k = j.Add(ActiveSheet, , "My_Slicer_Date", "Date", 0, 0, 100, 195)

k.Top = 45
k.Left = 0

k.Style = "SlicerStyleLight4"

k.RowHeight = 15
k.ColumnWidth = 80

ActiveSheet.Shapes.Range(Array("My_Slicer_Date")).Select
Selection.Placement = xlFreeFloating

End Sub

 

[Martin_H]

Used this instead.

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

Set SLcache = wb.SlicerCaches.Add2(pt, "Date", _
"Date_July")

Set SL = SLcache.SLICERS.Add( _
ws, , "Date_July", "Date")

SL.Top = 45
SL.Left = 0

SL.Style = "SlicerStyleLight4"

SL.RowHeight = 15
SL.ColumnWidth = 80

ActiveSheet.Shapes.Range(Array("Date_July")).Select
Selection.Placement = xlFreeFloating