Excel VBA - Freeze Worksheet Calculation?
 

Hi,

Is there any way to freeze (prevent) a number of worksheets from
recalculating when a VBA macro is running?

I have a spreadsheet with about 10 worksheets, most of which are linked
to each other in some way. One of my macros only needs to use the
formulas (and related results) from about 7 of these, so the other 3
are updating all the time but since the results from these are not
needed then they are slowing down my code.

Is there some VBA function for temporarily disabling a worksheets
calculation function? I would need my code to look something like
this:


Code:
--------------------
Worksheet("sheet1").Calculate:False
Worksheet("sheet2").Calculate:False
Worksheet("sheet3").Calculate:False

*Execute the macro code*

Worksheet("sheet1").Calculate:True
Worksheet("sheet2").Calculate:True
Worksheet("sheet3").Calculate:True
--------------------


Is something like this possible?

Thanks
-Rob


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile: http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=470599

Worksheet.enablecalculation=false

"TheRobsterUK"
wrote in message
news:TheRobsterUK.1vxi2b_1127675107.1271@excelforu m-nospam.com...

Hi,

Is there any way to freeze (prevent) a number of worksheets from
recalculating when a VBA macro is running?

I have a spreadsheet with about 10 worksheets, most of which are linked
to each other in some way. One of my macros only needs to use the
formulas (and related results) from about 7 of these, so the other 3
are updating all the time but since the results from these are not
needed then they are slowing down my code.

Is there some VBA function for temporarily disabling a worksheets
calculation function? I would need my code to look something like
this:


Code:
--------------------
Worksheet("sheet1").Calculate:False
Worksheet("sheet2").Calculate:False
Worksheet("sheet3").Calculate:False

*Execute the macro code*

Worksheet("sheet1").Calculate:True
Worksheet("sheet2").Calculate:True
Worksheet("sheet3").Calculate:True
--------------------


Is something like this possible?

Thanks
-Rob


--
TheRobsterUK


------------------------------------------------------------------------
TheRobsterUK's Profile:
http://www.excelforum.com/member.php...fo&userid=9924
View this thread: http://www.excelforum.com/showthread...hreadid=470599

"TheRobsterUK"
wrote in message
news:TheRobsterUK.1vxi2b_1127675107.1271@excelforu m-nospam.com...

Hi,

Is there any way to freeze (prevent) a number of worksheets from
recalculating when a VBA macro is running?

Use properly in your macro:

Sheets("SheetName").EnableCalculation = True | False


Bruno