Dynamically set the value for Field in auto filter

[Guest]

I am filtering two separate data files by values contained in the column
titled 'City'. The position of this column in the 1st file is column 10,
however it is in column 9 in the second file. The filter statement below
works for the 1st file but not the 2nd.

Selection.AutoFilter Field:=10, Criteria1:="=*" & Sheets("My
Sheet").Range("A2").Value _& "*"

I need a way to accomodate the two files with the same statement. I can
find this column in both files using the names previously inserted by another
routine. If I could determine the column number then I could set field = the
derived column number.

Any suggestions?

 

[Mallycat]

I need a way to accomodate the two files with the same statement.

Why do you need to do this? Maybe just use an if statement

If Selection.Worksheet.Name = "Sheet1" Then
enter your first formula
Else
enter your second formula
end i