Gus the Snail


Hi there

I have a curious situation. I have written a macro in VBA for excel 2003 which, from a master workbook scrolls through 46 other workbooks, opening each one in turn and copying/pasting a number of ranges. It then saves and closes the workbook and moves on to the next. When I execute this macro from within the VBA editor, it runs quite quickly (about 5 seconds per sheet, which is acceptable). When I run the macro from a button on one of the sheets in the master workbook however, it runs incredibly slowly and in fact seems to stop altogether. If I break the execution, then click debug and recommence running the code from within the editor, it runs fine again. Can anyone suggest why this might be

I'd be grateful for any light anyone could shed.

Gus





Re: Macro running slowly when run from button

Joe Dawson


I would suggest two things:

1. turn off calculation just after each spreadsheet is opened because that setting is changed to whatever the setting on the last spreadsheet opened is:
application.Calculation = xlCalculationManual

2. Run the macro directly from the VBE and then save the spreadsheet and then run it again from the button. I have found that sometimes when I run a macro from a button it does not see changes I made to the procedure it calls until I run that procedure directly from the VBE. Once I run it from the VBE, then the button will see it. I say this because I wonder if you changed the code and the button does not 'see' that.







Re: Macro running slowly when run from button

ADG

hI

In addition to the above, also turn off screen refreshing at the start of the macro and turn it back on at the end:

Application.ScreenUpdating = False

...

Application.ScreenUpdating = True






Re: Macro running slowly when run from button

Gus the Snail

Thanks for your help. I think saving it before re-running it seems to have helped. When I opened it up today and ran it from the button, it was quit a lot quicker.




Re: Macro running slowly when run from button

Gus the Snail

Cool, cheers. It all seems to running OK now.