I am trying to create a automatic filter with listobjects in excel VBA but it is giving me a runtime error 9. I have checked to make sure that all of the sheet names and table names match but I can't figure it out.
Error is thrown on this line
Set lo = qws.ListObjects("filter")
Sub filter()
Dim qws As Worksheet 'Power Query
Dim treews As Worksheet 'Element Tree worksheet
Dim pos As String 'position string used to search in the query worksheet
Dim lo As ListObject 'area to filter
Set qws = ThisWorkbook.Worksheets("PQuery")
Set treews = ThisWorkbook.Worksheets("Element Tree")
Set usedrng = qws.UsedRange
'record position that will be recognized by query worksheet
pos = treews.Cells(4, 2)
Debug.Print pos
Set lo = qws.ListObjects("filter")
'clear the autofilters from the query worksheet
qws.AutoFilterMode = False
'Filter by position for "all" and "BT", research how to make these user inputs so it is
'Easy to switch from one position to another
lo.Range.AutoFilter Field:=3, Criteria1:="=**All**", Operator:=xlOr, _
Criteria2:="=*" & pos & "*"
End Sub
Bookmarks