fixed filter criteria made variable
Thanks JAcob! I will try it and let you know how I get on. I am assuming that
if I change the table name to "sony vG" it is no problem.
"Jacob Skaria" wrote:
Try the below macro.. The below points are assumptions/criterias for the
below to work
--Workbook and table name are same sonyapps.xls
--Key word can be input by the user
--The table is to be in sheet 2009
Dim strTable As String, strKey As String
strTable = Replace(ActiveWorkbook.Name, _
Mid(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".")), "")
strKey = InputBox("Filter by word")
ActiveWorkbook.Sheets("2009").Activate
ActiveSheet.ListObjects(strTable).Range.AutoFilter Field:=1, _
Criteria1:="=*" & strKey & "*", Operator:=xlAnd, _
Criteria2:="=*/" & strKey & "*"
If this post helps click Yes
---------------
Jacob Skaria
"goodfish" wrote:
Hi, I have recorded a macro which runs fine for a specific workbook (the one
I recorded it on) but I need to adapt it so that it can run on any customer's
workbook.
It filters data from table "sonyapps", sheet "2009", according to filter
criteria: contains "sony".
Then it pastes filtered data onto new worksheet
and then performs another filter on the already filtered data, according to
filter criteria contains "/sony" and then the macro continues.
Now when I run the macro on customer workbook "epson", for each of those
instances I would like the macro to substitute the word "sony" with the word
"eps" (which is the code i give the customer epson).
Is there a way to do this (with a single popup box preferably) or do I have
to record 15 similar macros? maybe the table/range can be selected without a
name as it is the only table in the worksheet, how would I do that?
here are the instances:
Range("sonyapps").Select
Range("Q130").Activate
ActiveSheet.ListObjects("sonyapps").Range.AutoFilt er Field:=1, Criteria1:= _
"=*sony*", Operator:=xlAnd
ActiveSheet.Range("$A$1:$Q$17").AutoFilter Field:=1, Criteria1:="=*/sony*", _
Operator:=xlAnd
(ALSO is the "Q130" (the end cell of table "sonyapps") and the "$A$1:$Q$17"
(the original pasted table) going to adapt themselves,?)
I appreciate any help, thanks.
|