Make an slicer in vba excel automatically

[Hannah123456]

Hello,
I am an student working in an intership that was assinged with a proyect. I am trying to make a vba macro that can make a dashboard out of any table in an excel, and I am stuck making the slicers. My issue is that, since the point is for it to be able to work in any document, I cant specify what fields I want to be made into slicers. My idea was that the ones selected to be rows in the pivot table will be made into slicers and the part of making the pivot table based on that is already done. But I cant figure out how to make the slicers without specifying the name of the field.



this one worked to make the slicers, but they are specified with a name what slicers i want, which i cant do

' Add a slicer for the "VP" field Set slicerCache = ThisWorkbook.SlicerCaches.Add( _ pvtTable, "VP")
    Set slicer = slicerCache.Slicers.Add( _         pvtSheet, , "VP Slicer", "VP", 15, 775, 100, 325)
    ' Add a slicer for the "ANEXOS" field     Set slicerCache = ThisWorkbook.SlicerCaches.Add( _         pvtTable, "ANEXOS")
    Set slicer = slicerCache.Slicers.Add( _         pvtSheet, , "ANEXOS Slicer", "ANEXOS", 15, 895, 100, 200)

I tried with this one and it identifies the first pivot field as the the row "VP", which means that that works, and i was planning on connecting that with a checkbox and an if true. But Set sc = pt.SlicerCaches.Add2(pt, fieldName, "Slicer for " & fieldName) here it says the object doesn't support this property or method.

Sub CreateSlicerForPivotField1()

    Dim pt As PivotTable
    Dim sc As slicerCache
    Dim s As slicer
    Dim wsPivot As Worksheet

    ' Find the "pivot" worksheet
    Set wsPivot = ThisWorkbook.Worksheets("pivot") ' Change "pivot" to your actual sheet name if needed

    ' Check if a pivot table exists on the worksheet
    If wsPivot.PivotTables.Count = 0 Then
        MsgBox "No pivot table found on the 'pivot' worksheet.", vbExclamation
        Exit Sub
    End If

    ' Get the first pivot table on the worksheet
    Set pt = wsPivot.PivotTables(1)

    ' Use the first field name for the slicer
    fieldName = pt.PivotFields(1).Name

    ' Add a slicer cache for the first field
    Set sc = pt.SlicerCaches.Add2(pt, fieldName, "Slicer for " & fieldName)

    ' Add the slicer to the same worksheet
    Set s = sc.Slicers.Add(wsPivot, , "Slicer for " & fieldName, fieldName)

    ' Position and format the slicer as needed
    s.Top = 50
    s.Left = 50
    s.Style = "SlicerStyleLight4"

End Sub

Can anyone help me

 

[Domenic]

First, the SlicerCaches object is the property of the Workbook object, not the PivotTable object. Secondly, the name of the SlicerCache should not contain spaces. Therefore, try the following instead...

Set sc = ThisWorkbook.SlicerCaches.Add2(pt, fieldName, "Slicer_for_" & fieldName)

Hope this helps!