Excel 2007 : Same Macro on more than one worksheet

**alans2449** · 03-25-2010, 02:05 AM

I am creating a workbook to cover a four day golf tournament. The workbook has four identical worksheets, each representing individual divisions 1 thro 4, which have the same number of players. I input scores against players in a particular division, progressively over the four days. When all the results are in for any particular day I use a custom, 5 level sort to determine the winner and runner up on that day, and again, at the end, to determine the overall winner and runner up. As the data in each particular day sort is different and there are 2 results, I have 10 macros set up for each sheet

I set up the macros on Sheet 1 to do this, but the macros only works on Sheet 1. I want to use the same macros on all four sheets. How do I do this please ??

Previously in Excel 2003, a macro set up on one sheet worked on all worksheets in the workbook, but apparently not in Excel 2007

**royUK** · 03-25-2010, 02:20 AM

Any macro code inserted in a worksheet event will only work on that worksheet. The worksheet module should only contain event code, not other Procedures. These should always be placed in a Standard Module,and can be called by an event. Excel 2007 is no different to earlier versions in this respect.

If you want code to run on any sheet then use ActiveSheet in the code placed in a Standard Module

**alans2449** · 03-25-2010, 04:53 PM

Hi Roy
Thanks for your prompt reply. However, I can assure you that I have created workbooks in Excel 2003, where my macros set up on one sheet DO work across all worksheets in the workbook.
In Excel 2007, when I look at the Macro, and go to "edit", I can see the following line
ActiveWorkbook.Worksheets("DIV 1").Sort.SortFields.Clear where "DIV 1" is worksheet title. As if it is possible to define the worksheets where the Macro will work, but I dont know how to extend to additional worksheets. Any thoughts ?????

**shg** · 03-25-2010, 05:09 PM

Please Login or Register  to view this content.

**alans2449** · 03-25-2010, 10:29 PM

Just in case there is some confusion, I do not want to apply the Macro to all sheets simultaneously, I just want to be able to use the same Macro on every worksheet as needed.

**shg** · 03-25-2010, 11:29 PM

What does 'as needed' mean?

**teylyn** · 03-26-2010, 01:37 AM

I can assure you that I have created workbooks in Excel 2003, where my macros set up on one sheet DO work across all worksheets in the workbook.

That may be the case. Roy's suggestion still holds. Event code in the Sheet modules, all other code in standard modules.

ActiveWorkbook.Worksheets("DIV 1").Sort.SortFields.Clear where "DIV 1" is worksheet title

If you want to apply that to a particular worksheet, you may need to set up a few lines of code that identify which sheet the code shall run on.

Could be the current sheet. Could be a sheet selected through a dialog with the user.

Not enough information.

**royUK** · 03-26-2010, 02:05 AM

Post an example of what you mean by a macro set upon a sheet

**alans2449** · 03-30-2010, 05:41 AM

Thanks again. Sorry for the delay in reply. Below is the Macro activated by Ctrl + g
Range("B6:CD55").Select
ActiveWorkbook.Worksheets("DIV1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("DIV1").Sort.SortFields.Add Key:=Range("BU6:BU55") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("DIV1").Sort.SortFields.Add Key:=Range("P6:P55"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("DIV1").Sort.SortFields.Add Key:=Range("Q6:Q55"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("DIV1").Sort.SortFields.Add Key:=Range("R6:R55"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("DIV1").Sort.SortFields.Add Key:=Range("N6:N55"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("DIV1").Sort
.SetRange Range("B6:CD55")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub

DIV1 is the title of the worksheet where the macro was created. I want to apply the same macro, to do an identical custom sort over an identical data range in worksheets titled DIV2, DIV3 & DIV4. How do I edit the lines in the Macro to include the extra sheets?? is it something like Worksheets ("DIV1", "DIV2") or ("DIV1"), ("DIV2") etc?? Please advise

**teylyn** · 03-30-2010, 06:12 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here