On 27 Mai, 17:08, molinari <dh0...@gmail.com> wrote:
> 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?
You have to use VB! The code you have pasted here looks very much
as if you have used the macro recorder. The macro recorder does
not give you the flexibility that you need in order to avoid
duplicated code.
> Sub Macro()
1. Give this Sub a telling name.
2. Give this Sub two parameters: One integer parameter that
gives the sheet number, and one string parameter that gives
the suffix "a" or "b", such as below:
Sub ProcessSheet (SheetNumber as Integer, SheetSuffix as String)
3. Put this Sub into separate Code Module (google for this step if you
don't know how to add a Code Module)
> Sheets("sheet1a").Select
This line would have to be replaced by
Sheets("sheet" & SheetNumber & SheetSuffix).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
I don't like it the way how the macro recorder does things, so
I'd replace the previous three statements by something like this:
Sheets("sheet" & SheetNumber & SheetSuffix).Cells.Clear
This line has the advantage that Excel will not longer update the
user interface during the execution of the macro (no nasty flicker
each time you select something).
However, replacing each occurance of "Sheets("sheet1a").Select"
by "Sheets("sheet" & SheetNumber & SheetSuffix)" is also not
quite good style, since this spreads the idea how you get the
sheet name from the two parameters throughout the code (if this
changes later on, you'd have to replace lots of lines). I'd
rather use the following code:
Dim CurrentSheet as Excel.Worksheet
set CurrentSheet = Sheets("sheet" & SheetNumber & SheetSuffix)
With this code you can simply say:
CurrentSheet.Cells.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
Changing this code into something more readable is left as a
exercise for you ;-) (it should give you some insight into
_real_ VBA programming)
> 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
Here you can use the parameters:
Selection.AutoFilter Field:=7, Criteria1:=SheetSuffix
> 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:="="""""
If the criterion cannot be derived from the sheet number directly,
you'll have to add another parameter to the Sub:
Sub ProcessSheet (SheetNumber as Integer, _
SheetSuffix as String, _
Criterion1 as String, _
Criterion2 as String)
> 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
Using copy-paste with Selects can be replaced by something like this
SomeSheet.Cells("Source spec").Copy AnotherSheet.Cells("Target Spec")
Note that this copy statement is not as flexible as the copy-paste
mechanism (you cannot specify that you want to copy only the values)
> End Sub
If you have finished overworking this Sub, you can write a new sub
that will process all sheets in one go:
Sub ProcessAllSheets
ProcessSheets 1, "A", "1111first", "1111second"
ProcessSheets 1, "B", "1111first", "1111second"
ProcessSheets 2, "A", "2222", ""
ProcessSheets 2, "B", "2222", ""
ProcessSheets 3, "A", "3333", ""
ProcessSheets 3, "B", "3333", ""
:
End Sub
I hope this helps to get you started.
Regards,
Stuart
--
----------------------------------------------------------------------------------
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 excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
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
