[SOLVED] Create a Slicer to Table in VBA

Create a Slicer to Table in VBA

I have a VBA Code where I dump the downloaded data from my database and it formats for what I need. I am trying to add a part of code that would automatically create a slicer and for one of my fields but it's giving me an error and I don't know how to fix it.

My goal is, after all the modifications I have to make in the CSV file, I add 14 row above my data, transform the data into a table, and add the slicer for that table where I have the clear 14 rows.

This is the code I have for now:

wsO.Rows("1:14").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
lRowSlicer = lRow + 14
wsO.ListObjects.Add(xlSrcRange, Range("$A$15:$AO$" & lRowSlicer), , xlYes).Name = "PO_Table"
wsO.ListObjects("PO_Table").TableStyle = ""
    
Set wb = ActiveWorkbook
wb.SlicerCaches.Add2(wsO.ListObjects("PO_Table"), "Supplier") _
    .Slicers.Add wsO, , "Supplier", "Supplier", 6, 6, 1049.76, 200
With wb.SlicerCaches("Slicer_Supplier").Slicers("Supplier")
    .NumberOfColumns = 4
    .RowHeight = 28.8
End With

when the VBA reaches the line

wb.SlicerCaches.Add2(wsO.ListObjects("PO_Table"), "Supplier") _
        .Slicers.Add wsO, , "Supplier", "Supplier", 6, 6, 1049.76, 200

Then it gives me the invalid procedure call or argument error. How can I make it work?

Solution

Just if someone ever have the same problem as me, i recreated my whole code and now it's working:

Dim pSlicers As Slicers
Dim sSlicer As Slicer

Dim pSlicersCaches As SlicerCaches
Dim sSlicerCache As SlicerCache

Set wb = Application.ThisWorkbook

Set pTable = wsO.ListObjects("PO_Table")

Set pSlicersCaches = wb.SlicerCaches
Set sSlicerCache = pSlicersCaches.Add2(pTable, "Supplier", "Supplier")
Set sSlicer = sSlicerCache.Slicers.Add(SlicerDestination:=wsO.Name, _
                                       Name:="SupplierSlicer", _
                                       Caption:="Supplier", _
                                       Top:=6, Left:=6, _
                                       Width:=Application.InchesToPoints(11.4), _
                                       Height:=Application.InchesToPoints(2.54))

'Modify Slicer
sSlicer.NumberOfColumns = 4
sSlicer.RowHeight = 28.8