Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed filter criteria made variable
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed filter criteria made variable
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
fixed filter criteria made variable
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! "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I made a cell reference variable? i.e. =sum(D4:D(4+A2)) | Excel Worksheet Functions | |||
Building criteria string for Advanced Filter variable not resolvin | Excel Discussion (Misc queries) | |||
Advanced Filter VB Script for Variable Criteria Range | Excel Programming | |||
Can the column index in a cell address be made variable? | Excel Programming | |||
How do I identify Filter criteria or variable graph title? | Excel Discussion (Misc queries) |