A little VBA help please

[ashfire]

Good afternoon.


Im after a little VBA help. Ive created a macro that filters a column based on a cell. However the macro reads this as “=TP351033C” when I really want to be able to change the cell to a new value and then rerun in future.


Im sure its got something to do with DIM but cant crack it cheers


Sample macro


Range("B4").Select

Selection.Copy

Sheets("Defects").Select

ActiveSheet.Range("$A$1:$AS$309").AutoFilter Field:=2, Criteria1:= _

"=TP351033C", Operator:=xlAnd

 

[vijaySharma]

Ashfire,


Assuming that the Filter criteria is in a sheet called "Input", define a Name "myFilterSrting" (for example) for the cell where you will ask the user to provide the filter.


Below is the VBA code


Dim strFilterString as string


strFilterString = [myFilterString]


ActiveSheet.Range("$A$1:$AS$309").AutoFilter Field:=2, Criteria1:= _

strFilterString, Operator:=xlAnd


HTH

~Vijay

 

[Luke M]

Do be careful in making sure you define which sheet you pull the criteria from vs which sheet you want filtered. I notice in the original post that you switched sheets, so you'd want to make sure that you either

a) always run macro when you are on the start sheet, or

b) have the macro define which sheet's cell B4 you want to use.

 

[ashfire]

so simple yet so powerful its brilliant. thank you both very much.