This is the challenge I'm facing:

I want to create macro's which are intended to pre-autofilter data in a sheet. However, if new columns get insterted or re-arranged (it's a living file), all macro's need to be changed again since the columns get shifted and so does the FIELD:= value of the Autofilter command. I want to keep the macro's flexible by making the Autofilter use relative Range names instead of 1 for column A, etc...

Current Example (filter col B on YES)

Selection.AutoFilter Field:=2, Criteria1:="YES"

Desired Example (filter name defined Range on YES)

Selection.AutoFilter Field:=RANGENAME, Criteria1:="YES"

With the RANGENAME I want to define a range like cell B2 until B10 [=Sheet1!$B$2:$B$10]

I think I have to define a variable with DIM, but am getting stuck in my trail and error process... any help is really aprreciated!

Thanks,
BA