Macros slowing down workbook

[Guest]

I have 31 worksheets in a workbook each worksheet contains the same macros,
I have two questions.
1.Can the macros be saved in a central place so they are not run on the
startup of the workbook 31 times (I copied the macro into each sheet!)
2. Can the macros be set to only run if the worksheet is clicked on rather
than having them run from the start up of the workbook.
As you can imagine my workbook is running very slow!
Any other speed up tips will be appreciated

 

[Frank Kabel]

Hi
you may provide some more details about your macros. Do you run them
all at startup?. Really execute them?

Of course you can put your macros in the workbook_event sheet_activate
to run them only if a sheet is selected

 

[Guest]

Hello,

Of course you can put your macros in the workbook_event sheet_activate
to run them only if a sheet is selected

Sorry I'm not being to clever!, where do I write this?

 

[Dave Peterson]

When you're in the VBE, hit ctrl-r to see the project explorer (much like
windows explorer).

Find your workbook/project and hit the asterisk on the numeric keypad to expand
all the levels.

You'll see "microsoft excel objects"

under that, you'll see the individual sheets and one called "ThisWorkbook".

ThisWorkbook contains events that are associated with the workbook. Instead of
having 31 worksheet_activates (one under each sheet module), you can put the
code (maybe some minor changes) in the Workbook_SheetActivate procedure.

When you're in the ThisWorkbook module, use the dropdowns on the top of the
codewindow (usually the right hand side). Choose Workbook from the left
dropdown and then browse through the list of events in the rightmost dropdown.
You'll see Workbook_SheetActivate in there.