Same sub in many worksheets (Excel 2003 SP3)

Topic

New Reply

Good evening

I am just about to start the process of rebuilding a workbook because of some sort of cell reference problem. Before I start I would like to ask a question that may seem simple to experienced users so please excuse my ignorance.

I have the same sub running in 9 different worksheets, would it be more effecient to make it a macro (not quite sure how yet) and then use some sort of reference in each of the worksheets that calls the macro so that it is only stored once.

Thanks

Reply | Quote

Replies

What Triggers the Sub to Run?

If it is worksheet event, then you can possibly look at one of the workbook events instead

many Sheet Events have a workbook equivalent that passes both the sheet name and other parameters.

Reply | Quote

You can put the macro in a personal.xls module and run it thus from whatever event or button you want it triggered in your workbooks:

Run “Personal.xls!MyCode”

or if it needs arguments:

Run “Personal.xls!MyCode”, , , , … etc

Reply | Quote

On the basis that it’s the same workbook as you mentioned here, can you give us some links to your previous threads so we can get a better feel for what the Worksheet Code is?

It may be the case that previous advice avoided combining the code – or it may just be that JohnBF’s proposal is smething that simply got overlooked.

Reply | Quote

On the basis that it’s the same workbook as you mentioned here

If it’s a distributed workbook, having all its users add code to personal.xls may not be practical.

Reply | Quote

On the basis that it’s the same workbook as you mentioned here, can you give us some links to your previous threads so we can get a better feel for what the Worksheet Code is?

It may be the case that previous advice avoided combining the code – or it may just be that JohnBF’s proposal is smething that simply got overlooked.

Hi Malcolm

I am trying to implement Johns Suggestion but I am quite slow at learning new things, it occurs to me though that a personal Macro may bot be relevant as I share the workbook with 2 others. I am currently searching for an article that was posted in the lounge, I think by Don? on personal.xls to help me along. And yes you are right it is linked to the following post and I do realise that my workbook has become a monster over 3 years so I am trying to tidy it up at the same time as rebuilding it.

Thanks for the input

Reply | Quote

I share the workbook with 2 others.

Then it’s time for you to learn about personal.xls! Are you the only person who runs the macros? If so, personal.xls is your best bet. Even if the other two do, with only three “installations” to maintain, personal.xls may be the easiest solution. Search this Forum for creating and using personal.xls. These may also be helpful http://office.microsoft.com/en-us/excel/HA010872961033.aspx and http://www.rondebruin.nl/personal.htm.

Reply | Quote

A Public Sub in a Module can be called from any worksheet. This is also where custom worksheet functions go.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote