I'm trying to run a macro that will pull data from a Table in Excel, when the Table might initially be filtered.
The code should:
I want to use the Column Header name: "STATUS", because the Table has changed a few times, and the Index could also change.
Through various researches, and attempts, I think I have the correct line that will clear the filter WITHOUT removing filters from the table.
However, I am now getting the: "AutoFilter method of Range class failed" on Step 2:
Sub OpenPOs()
Dim wbInventory As Workbook
Set wbInventory = Workbooks("IWI Inventory MASTER Report.xlsb")
With wbInventory
With wsPOTracker 'This is a Code name for worksheet within the workbook
'Clear the Table Filter
If Not .ListObjects("Table1").AutoFilter Is Nothing Then .ListObjects("Table1").AutoFilter.ShowAllData
'Apply Table Filter to the Column: "STATUS" Criteria = "O"
.ListObjects("Table1").ListColumns("STATUS").Range.AutoFilter , Criteria1:="O" '<Run-time error '1004': AutoFilter method of Range class failed
'Rest of the code
Links I've tried to research/copy from:
Try this:
Dim lo As ListObject
Set lo = wsPOTracker.ListObjects("Table1") 'reference the listobject/table
If Not lo.AutoFilter Is Nothing Then lo.AutoFilter.ShowAllData
lo.Range.AutoFilter Field:=lo.ListColumns("STATUS").Index, Criteria1:="O"