Hi All,
It's been awhile since I posted. I've been working with ChatGPT to come up with what I thought was a relatively simple filtering script, however, have come to the point where the AI can't figure out the slicer error, and Im not great with the slicer code...so I've come to the real experts to seek advice and guidance on the forum. Any help is appreciated.
I'm trying to filter data for further analysis on an excel file that comes premade from other applications. The excel file has 4 slicers already at the top of the sheet, and a huge table with headers starting in cell A11 and the data in A12.
The concept is that the script recognizes column's C,D,H as business line, market sectors, and project organization and filters the table data down to column C and D being BL004 and MS030, respectively.
Afterwards, all existing slicers are removed and replaced with a single one that slices the remaining that data in column H "project org" so that it is able to be viewed and filters easily by the user.
ChatGPT got me pretty far, but is failing on the line:
Set slicerObj = slicerCache.Slicers.Add(ws, filterColumnH, Left:=10, Top:=10, Width:=200, Height:=100)with the error "Runtime error 5: invalid procedure call or argument"
Any help would be appreciated.
Code is here:
Sub FilterTableWithSlicer()
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range
Dim filterColumnC As Range
Dim filterColumnD As Range
Dim filterColumnH As Range
Dim filterValue As String
' Set the worksheet
Set ws = ThisWorkbook.Worksheets("PAR Task Level") ' Replace "PAR Task Level" with the actual sheet name
' Set the table
Set tbl = ws.ListObjects(1) ' Assumes the first table on the sheet
' Set the filter column for column C
Set filterColumnC = tbl.ListColumns("Business Line").Range
' Set the filter column for column D
Set filterColumnD = tbl.ListColumns("Market Sector").Range
' Set the filter column for column H
Set filterColumnH = tbl.ListColumns("Project Organisation").Range
' Apply the filters
tbl.Range.AutoFilter Field:=filterColumnC.Column, Criteria1:="BL004"
tbl.Range.AutoFilter Field:=filterColumnD.Column, Criteria1:="MS030"
' Delete existing slicer (if exists)
Dim slicerName As String
slicerName = "ProjectOrgSlicer"
On Error Resume Next
ws.Shapes(slicerName).Delete
ThisWorkbook.SlicerCaches("Project Organisation").Delete
On Error GoTo 0
' Add slicer for column H
Dim slicerCache As slicerCache
Set slicerCache = ThisWorkbook.SlicerCaches.Add2(tbl, "Project Organisation")
Dim slicerObj As Slicer
Set slicerObj = slicerCache.Slicers.Add(ws, filterColumnH, Left:=10, Top:=10, Width:=200, Height:=100)
slicerObj.Name = slicerName
' Clear previous filtered data (excluding the header row)
Set rng = tbl.DataBodyRange
rng.Offset(1).Resize(rng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Hidden = False
End Sub
