VBA: Using Table column name instead of column number

[LAAdams17]

I have the following VBA:
ActiveSheet.ListObjects("Dashboard").Range.AutoFilter Field:=64, Criteria1 _
:="Adams, Lon"

The problem is when I move columns, my VBA quits working. I'd prefer to use a table column name like this instead:
ActiveWorkbook.Worksheets("Dashboard").ListObjects("Dashboard").Sort.SortFields _
.Add Key:=Range("Dashboard[BC Owner]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal

Can someone help me rewrite the first line of code above to use the table column name instead of column number?

Thank you!
Lon

 

[JoeMo]

The autofilter method requires an integer input for the field argument so you can't insert the table column name directly. Here's one way you can do you want.

Sub testit()
Dim myField As Long, myHdr As String
myHdr = "BC Owner"
With ActiveSheet.ListObjects("Dashboard").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="Adams, Lon"
End With
End Sub

 

[perle003]

This works perfectly for me without a ton of variable definitions needed. put the cursor in the required column header, then use ActiveCell.column to determine the number of the column ...

Range("TableName[[#Headers],[ColumnName]]").Select
ActiveSheet.ListObjects("PivotName").Range.AutoFilter Field:=ActiveCell.column, Criteria1:= Variable, Operator:=xlFilterValues

 

[Peter_SSs]

This works perfectly for me without a ton of variable definitions needed.

.. but only if the ListObject starts in column A.

Can someone help me rewrite the first line of code above to use the table column name instead of column number?

Give this a try

With ActiveSheet.ListObjects("Dashboard")
  .Range.AutoFilter Field:=.ListColumns("BC Owner").Index, Criteria1:="Adams, Lon"
End With