Thanks all, I did something like the looping method, and ended up
getting into much more VB programming (and less macro-recording).
Now my next question is, how to read data from a worksheet into an
array, where I want it to dynamically scale as people add to the
worksheet. I.e., there is a header row and column, which I don't want
to have entered in, and people can add new information to the table,
which I would want to account for. I.e., I can't just tell it to add
cells A2:D10 to the array.
On May 28, 2:35 am, Stuart Redmann <dertop...@web.de> wrote:
> 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
