note: "postfix" = "suffix" it sounds like you're wanting to run a macro that varies based on the sheet name, and operates on the sheet selected?
therefore, you could use: select case (ucase(right(activesheet.name,1)) case "A" FieldName = "A" case "B" FieldName = "B" end Select use this to set variables for the different sheet types and use these variables throughout the macro if you're wanting to process ALL sheets, use a loop: for I = 1 to sheets.count shtname = sheets(I).name ..... next I does this help? Paul ________________________________ From: molinari <[email protected]> To: MS EXCEL AND VBA MACROS <[email protected]> Sent: Thu, May 27, 2010 11:08:42 AM Subject: $$Excel-Macros$$ eliminating repetitive code in a macro I have a macro for which I want to eliminate repetitive code. I have two sets of sheets, which match in different ways and are systematically labelled. There are sheets with numbers 1 to n, which also have a postfix "a" or "b". Different things need to be done to the sheet depending on both the sheet number and the sheet letter; such that the entire macro -- as illustrated below for sheet1a -- will be different for each different sheet (i.e., sheet1a, sheet1b, sheet2a, sheet2b, etc). I actually have 16 different sheets, so this code owuld need to be repeated 16 times, with slight modifications. I'm looking for a way to eliminate that, and not repeat this block of code over and over again, but do specific things differently depending on what sheet it is operating on. How can I accomplish this? I've attached a link to the .bas file that I've uploaded, and pasted the macro text below: http://excel-macros.googlegroups.com/web/Macro.bas?gsc=-Reg5wsAAAAgqiTzXmXRZ2zlLirISLnw Sub Macro() Sheets("sheet1a").Select ' This will be different for each sheet operated on ' when searching for Field7 = "A": "sheet1a", "sheet2a", "sheet3a", "sheet4a" ' when searching for Field7 = "B": "sheet1b", "sheet2b", "sheet3b", "sheet4b" Cells.Select Selection.Clear Sheets("MasterSheet").Select Cells.Select Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("H2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Cells.Select Selection.AutoFilter Selection.AutoFilter Field:=7, Criteria1:="A" ' This line will be different for the "sheet#a" and "sheet#b" sheets; e.g., will be different for "sheet1a" and "sheet1b" sheets, etc. ' "A" for all "sheet#a" tabs ' "B" for all "sheet#b" tabs Selection.AutoFilter Field:=8, Criteria1:="=1111first", Operator:=xlOr, _ Criteria2:="=1111second" ' This line will be diff for each sheet operated on (but will be the same for equivalent "sheet#a" and "sheet#b" sheets); e.g., it will be the same ' for "sheet1a" and "sheet1b" 'for "sheet1"[a/b] tabs: Criteria1:="=1111first" Criteria2:="=1111second" 'for ..."sheet2"[a/b] tabs: Criteria1:="=2222" Criteria2:="=""""" 'for ..."sheet3"[a/b] tabs: Criteria1:="=3333" Criteria2:="=""""" 'for ..."sheet4r"[a/b] tabs: Criteria1:="=4444" Criteria2:="=""""" Selection.Copy Sheets("sheet1a").Select ' This line will be diff for each sheet operated on; e.g., for sheet1a, sheet1b, sheet2a, sheet2b, sheet3a, sheet3b, sheet4a, sheet4b Range("A1").Select ActiveSheet.Paste End Sub -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
