Using a named range in autofilter Field

[mecerrato]

I use autofilter for a bunch of things from corporate reports. However, every now and then corporate changes the fields around which messes up my macros. If I am able to use named ranges instead of column numbers in the autofilter code I can fix really easy by just changing the column referenced in the name range.

Sheets("Current Pipeline").Range("$A$1:$AI$150000").AutoFilter Field:=4, Criteria1:=LC

I haven't been able to find a way to do it, any thoughts?

 

[Tetra201]

Maybe

Sheets("Current Pipeline").Range("$A$1:$AI$150000").AutoFilter Field:=Range("MyField").Column, Criteria1:=LC

 

[kevin9999]

Do they change the names of the headers as well, or just change the order?

 

[mecerrato]

I was able to make it work using this code from stackoverflow forum:

Dim LC As String
Dim LOcIndex As Long
LC = Sheets("Summary").Range("B21").Value
LOcIndex = Application.Match("LO", Sheets("Current Pipeline").Range("A1:AK1"), 0)
Sheets("Current Pipeline").Range("$A$1:$AI$150000").AutoFilter Field:=LOcIndex, Criteria1:=LC

 

[Joe4]

Cross posted at: Using a named range in autofilter Field

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

If you have posted the question at more places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.