Macros slowing down workbook

G

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
 
F

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
 
G

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?
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top