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:
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