Excel 2003 automatic calculation all worksheets except one

[Tenacity9]

Re: the setting in Tools==>Options==>Calculation (Automatic or Manual) , can
it be applied to a single worksheet within a workbook or at least single
workbook? I want to have calculation be automatic in all worksheets except
one, where I want it set to manual. Can this be done? How?

I don't want to reset Tools==>Options==>Calculation (Manual) each time I
open the Manual Calc workbook. I'd like Calc to be defaulted to Automatic,
and when I open the one manual WB, calc is at once set to manual only for
that WB.

If this needs VBA, it will be much appreciated if you specify the exact
wording for me to cut and paste since I'm not a VBA expert.

It's ok, although not the preferred way, if the VBA makes the calc manual
without my having to reset Tools==>Options==>Calculation (Manual) each time ,
but also manual for all workbooks opened after the VBA-manual calc workbook
is opened, since presumably I can re-start a separate instance of Excel
containing only this VBA-manual calc workbook, and open all my other WB in
the first instance of Excel where the program was re-loaded separately. Will
this work, ie, Manual calculation automatically selected by your VBA will
apply only to open workbooks in one instance of Excel, while not affecting
automatic calc workbooks opened in another instance of Excel started in
parallel on the same machine?

Separate question. When I open several windows of Excel workbooks, they
cannot be moved so one workbook is on one monitor and another workbook is on
my second dualview monitor. They can only be viewed on the same monitor as
each other open workbook. The only way I can split a workbook to the other
screen, while another workbook is on the main screen, is to open another
instance of Excel for that workbook and it can then be split. Is there a way
to split workbooks opened in the same instance of Excel to 2 monitors without
having to open another instance of Excel?

Thanks for your help.

 

[Shane Devenshire]

Hi,

this is an Excel wide option. However, in code you can use something like
this

Sub StopRecalc()
Dim sh
For Each sh In Worksheets
Worksheets(1).EnableCalculation = False
Next sh
End Sub

To recalculate the sheet you will then need to run code the sets this to True.

 

[Dave Peterson]

You can turn off calculation in any worksheet (xl2003 and above, I _think_).
But that has to be done via code.

Option Explicit
Sub TurnOffCalc()
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets("Sheet999")
wks.EnableCalculation = False
End Sub
Sub TurnOnCalc()
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets("Sheet999")
wks.EnableCalculation = True
End Sub

If you're using multiple monitors, you may want to review Chip Pearson's notes:
http://cpearson.com/excel/MultipleMonitors.aspx

 

[Charles Williams]

Dave,

..EnableCalculation is available from Excel 97, not sure about earlier
versions.

also note that this property is not stored when you save a workbook, and
when you switch enablecalculation back on you get a full calculation rather
than a recalculation for the sheet at the next calculate.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com