I have a very lenghty macro that I created in excel to distribute a large
list of data to other sheets in the workbook based upon assignments denoted
on the sheet containing the data listing. As such, my macro simply repeats
the same commands for each of the "other sheets" that the data is being
distributed to. Is there a way to simplfy macros like this in excel? (i.e.
refer to a separate macro containing the repeated commands or refer to
specific lines in the macro rather than redundency)
--
Erik

Hi Erik,

Try something like:

Dim SH as Worksheet

For Each SH in ActiveWorkbook.Worksheets
' Your common sheet processing code here
'Or, call another macro which holds the common code:
Call MyMacro
Next SH

---
Regards,
Norman



"Erik" wrote in message
...
I have a very lenghty macro that I created in excel to distribute a large
list of data to other sheets in the workbook based upon assignments
denoted
on the sheet containing the data listing. As such, my macro simply
repeats
the same commands for each of the "other sheets" that the data is being
distributed to. Is there a way to simplfy macros like this in excel?
(i.e.
refer to a separate macro containing the repeated commands or refer to
specific lines in the macro rather than redundency)
--
Erik

Perhaps a loop through the worksheets collection:

For Each sh in Worksheets
Do something neat
Next sh

Mike F
"Erik" wrote in message
...
I have a very lenghty macro that I created in excel to distribute a large
list of data to other sheets in the workbook based upon assignments
denoted
on the sheet containing the data listing. As such, my macro simply
repeats
the same commands for each of the "other sheets" that the data is being
distributed to. Is there a way to simplfy macros like this in excel?
(i.e.
refer to a separate macro containing the repeated commands or refer to
specific lines in the macro rather than redundency)
--
Erik

Here is a section of the macro:

Sheets("A Brown").Select
Range("A7").Select
ActiveCell.FormulaR1C1 = "=IF(Master!RC8=R1C1,Master!RC, )"
Range("A7").Select
Selection.Copy
Range("A7:H555").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("H7"), Order1:=xlDescending,
Key2:=Range("A7") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A7:A555").Select
Selection.NumberFormat = "_(* #,##0.000_);_(* (#,##0.000);_(*
""-""???_);_(@_)"
Range("C7:D555").Select
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*
""-""??_);_(@_)"
Range("E7:E555").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("A7").Select
Sheets("A Ohara").Select
Range("A7").Select
ActiveCell.FormulaR1C1 = "=IF(Master!RC8=R1C1,Master!RC, )"
Range("A7").Select
Selection.Copy
Range("A7:H555").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("H7"), Order1:=xlDescending,
Key2:=Range("A7") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A7:A555").Select
Selection.NumberFormat = "_(* #,##0.000_);_(* (#,##0.000);_(*
""-""???_);_(@_)"
Range("C7:D555").Select
Selection.NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(*
""-""??_);_(@_)"
Range("E7:E555").Select
Selection.NumberFormat = "mm/dd/yy;@"
Range("A7").Select


As you can see, with the exception of the sheet selection command, all of
the commands from sheet to sheet are identical. I think that a loop would
work, but I am not sure how to incorporate that into the macro (because it
would be a first for me). Do you know how to structure that?

--
Erik


"Mike Fogleman" wrote:

Perhaps a loop through the worksheets collection:

For Each sh in Worksheets
Do something neat
Next sh

Mike F
"Erik" wrote in message
...
I have a very lenghty macro that I created in excel to distribute a large
list of data to other sheets in the workbook based upon assignments
denoted
on the sheet containing the data listing. As such, my macro simply
repeats
the same commands for each of the "other sheets" that the data is being
distributed to. Is there a way to simplfy macros like this in excel?
(i.e.
refer to a separate macro containing the repeated commands or refer to
specific lines in the macro rather than redundency)
--
Erik

Norman,
I tried your suggestion using a Call command and it seemed to work. I was
not familiar with the command. Thank you for the advice. :)
--
Erik


"Norman Jones" wrote:

Hi Erik,

Try something like:

Dim SH as Worksheet

For Each SH in ActiveWorkbook.Worksheets
' Your common sheet processing code here
'Or, call another macro which holds the common code:
Call MyMacro
Next SH

---
Regards,
Norman



"Erik" wrote in message
...
I have a very lenghty macro that I created in excel to distribute a large
list of data to other sheets in the workbook based upon assignments
denoted
on the sheet containing the data listing. As such, my macro simply
repeats
the same commands for each of the "other sheets" that the data is being
distributed to. Is there a way to simplfy macros like this in excel?
(i.e.
refer to a separate macro containing the repeated commands or refer to
specific lines in the macro rather than redundency)
--
Erik