Dynamic Field:= in Autofilter

[Spichael]

Hi there, I have the following code for a report. This code takes 60 plus fields and hides a bunch of them, autofilters based on criteria and sorts. My issue is that the source data changes from time to time which would throw off my filters. I take care of this in other areas by using the CreateNames in the top of the code and the Case statements in the middle of the code. But I cannot find a way to cover this in the AutoFilter Field:=1 area. I would like it to say something like Field:=ANR or something similar. The ANR column will always be in column AH. I appreciate your assistance.



Private Sub EDUANR_Click()
Dim c As Range
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Range("B1").Select
Selection.CurrentRegion.Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False

Cells.EntireColumn.AutoFit

For Each c In Range("B1:" & Range("B1").End(xlToRight).AddressLocal(False, False))
Select Case c.Value
Case Is = "MI Trace (709)"
c.ColumnWidth = 15
Case Is = "House Bill"
c.ColumnWidth = 15
Case Is = "Container Number"
c.ColumnWidth = 15
Case Is = "Origin"
c.ColumnWidth = 8
Case Is = "Vessel and Voyage"
c.ColumnWidth = 24
Case Is = "Carrier"
c.ColumnWidth = 40
Case Is = "Consignee"
c.ColumnWidth = 40
Case Is = "EDU"
c.ColumnWidth = 10
Case Is = "ANR"
c.ColumnWidth = 10
Case Else
c.ColumnWidth = 0
End Select
Next c
Range("B1").Select
ActiveSheet.AutoFilterMode = False
Range("B1").AutoFilter Field:=37, Criteria1:="<>"
Range("B1").AutoFilter Field:=33, Criteria1:="="

Range("B1:BZ1782").Sort Key1:=Range("Vessel_and_Voyage"), Order1:=xlAscending, Key2:=Range("EDU"), _
Order1:=xlAscending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub