I have written vba code to insert a slicer for each table in each workbook of the sheet. the code errors with Run-time error '5': Invalid procedure call or argument.

Dim ws As Worksheet
    Dim tbl As ListObject
    Dim slicerCache As slicerCache
    Dim slicer As slicer
    Dim columnName As String
    Dim slicerName As String

    ' Set the column name for the slicer
    columnName = "Request" ' Change to your column name

    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the worksheet contains a table
        If ws.ListObjects.Count > 0 Then
            ' Loop through each table in the worksheet
            For Each tbl In ws.ListObjects
                ' Add a slicer cache for the table
                Set slicerCache = ThisWorkbook.SlicerCaches.Add(tbl, columnName)

                ' Create a unique slicer name
                slicerName = ws.Name & "_" & tbl.Name & "_" & columnName

                ' Add a slicer to the worksheet
                Set slicer = slicerCache.Slicers.Add(ws, , columnName, slicerName, 100, 100, 200, 200)
            Next tbl
        End If
    Next ws

When I close the error, the slicer appears on the first worksheet, but nothing appears on the next.

When I click on the debug option for the error the line of code that it highlights is Set slicer = slicerCache.Slicers.Add(ws, , columnName, slicerName, 100, 100, 200, 200)

I have written vba code to insert a slicer for each table in each workbook of the sheet. the code errors with Run-time error '5': Invalid procedure call or argument.

Dim ws As Worksheet
    Dim tbl As ListObject
    Dim slicerCache As slicerCache
    Dim slicer As slicer
    Dim columnName As String
    Dim slicerName As String

    ' Set the column name for the slicer
    columnName = "Request" ' Change to your column name

    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Check if the worksheet contains a table
        If ws.ListObjects.Count > 0 Then
            ' Loop through each table in the worksheet
            For Each tbl In ws.ListObjects
                ' Add a slicer cache for the table
                Set slicerCache = ThisWorkbook.SlicerCaches.Add(tbl, columnName)

                ' Create a unique slicer name
                slicerName = ws.Name & "_" & tbl.Name & "_" & columnName

                ' Add a slicer to the worksheet
                Set slicer = slicerCache.Slicers.Add(ws, , columnName, slicerName, 100, 100, 200, 200)
            Next tbl
        End If
    Next ws

When I close the error, the slicer appears on the first worksheet, but nothing appears on the next.

When I click on the debug option for the error the line of code that it highlights is Set slicer = slicerCache.Slicers.Add(ws, , columnName, slicerName, 100, 100, 200, 200)

• excel
• vba

• 2 As a side note: it's a bad practice to name variables identically to object names. Consider naming slicerCache as sc, sCache etc. – Soren V. Raben Commented Mar 11 at 12:29

1 Answer 1

You have simply mixed the parameters for the slicer name and the caption. You set columnName as third parameter, but that should be the slicer name.

That works for the first table, but for the next table the name is already in use. Have a look to the slicer that was created on the first sheet: It's caption is what you intended to have as slicer name. My test data has a slicer to column "Quality":

Just exchange the third and forth parameter, that should do the trick:

slicerCache.Slicers.Add ws, , slicerName, columnName, 100, 100, 200, 200

Now the slicer gets the correct caption and code runs successfully over more than one table:

See Microsoft documentation:

Syntax

expression.Add (SlicerDestination, Level, Name, Caption, Top, Left, Width, Height)