fixed filter criteria made variable

[goodfish]

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.AutoFilter 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.

 

[Jacob Skaria]

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

 

[goodfish]

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.

 

[goodfish]

hi Jacob! the code worked fine except I would have to change the workbook
names because the table names cannot be changed to the workbook names as
they contain spaces. I have tweaked it a bi so that now I don't select the
table range by name but by this way:
Sub aggiornacliente()
Dim rng As Range, strKey As String

strKey = InputBox("Quale cliente vuoi aggiornare?")

Sheets("2009").Select
Set rng = Sheets("2009").Range("A1").CurrentRegion
rng.Select
rng.AutoFilter Field:=1, _
Criteria1:="=*" & strKey & "*", Operator:=xlAnd

'the following statement removes the first or header row
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Set rng = rng.SpecialCells(xlVisible)

My question now is wether the formula you originally gave me to extract the
table name from the workbook name could be modified to extract the strKey
(i.e. the customer code) from a cell A3 containing the following:
Contract UE-SONY-10214/08 dd 03/03/08
where SONY is the customer code and may be 3 or 4 letters
And if this becomes the strkey could it be applied to select the table which
for any customer workbooks would be named "strKeyapps"?
As I said the way I have works already so if the formula/code is complicated
don't worry yourself.
Thanks again!