Auto Calculate - Some...
 

Is there some way I can tell Excel to auto calculate some sheets, but to
set others to manual? Essentially I have a bunch of sheets that all
need auto calc, but then another set that do final analysis and I'm only
interested in one of them at a time. If I could shut the others down it
would speed things up.

Incidentally, I'm using Excel 97 if that makes any difference.

Thanks...

Bill

There are 3 ways I know of to do something like this:

1. split the workbook into several workbooks, one containing all the "auto"
sheets and then one book for each of the "manual" sheets.
You will need links between the books as appropriate.
Then you can open the "auto" book and the "manual" book you are currently
interested in. Then when you want a different "manual" book you close the
one thats open and open the next one.

This will not work well if the links between the sheets are complex, and you
would need to have Excel in automatic mode so that the open "manual" book
would always calculate.

2. write some VBA that uses the enablecalculation property of the worksheets
to control whether they get automatically recalculated or not. This is not
all that straighforward.

3. use FastExcel mixed calculation mode.

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Bill Martin -- (Remove NOSPAM from address)"
wrote in message ...
Is there some way I can tell Excel to auto calculate some sheets, but to
set others to manual? Essentially I have a bunch of sheets that all need
auto calc, but then another set that do final analysis and I'm only
interested in one of them at a time. If I could shut the others down it
would speed things up.

Incidentally, I'm using Excel 97 if that makes any difference.

Thanks...

Bill

Charles Williams wrote:
There are 3 ways I know of to do something like this:

1. split the workbook into several workbooks, one containing all the "auto"
sheets and then one book for each of the "manual" sheets.
You will need links between the books as appropriate.
Then you can open the "auto" book and the "manual" book you are currently
interested in. Then when you want a different "manual" book you close the
one thats open and open the next one.

This will not work well if the links between the sheets are complex, and you
would need to have Excel in automatic mode so that the open "manual" book
would always calculate.

2. write some VBA that uses the enablecalculation property of the worksheets
to control whether they get automatically recalculated or not. This is not
all that straighforward.

3. use FastExcel mixed calculation mode.

---------------

I've been pondering whether it's worth all the grief to split it up into
separate workbooks. I'm sort of philosophically opposed to that, but it
may be the pragmatic solution.

The VBA approach is what I wondered about. Sounds like one does exist.
I've got a VBA reference book on order so hopefully that'll arrive in
a few days and then I can stare at that approach a bit.

I guess I'll also stare at the spreadsheet some to see whether
suggestions from your web site help in this case. Possibly the one
about adding additional columns to reduce repeated calculations might
impact it significantly enough to be worthwhile.

Thanks Charles...

Bill

I think this capability was added in xl2002.

But you could use something like:

Option Explicit
Sub TurnOffCalc()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = "sheet1", "sheet3", "sheet5"
wks.EnableCalculation = False
End Select
Next wks

End Sub

To turn calculation off. (and "= True" to toggle it back on).

(Don't trust your output until you turn it back on for all the sheets--which
should recalc the sheets, too.)


"Bill Martin -- (Remove NOSPAM from address)" wrote:

Charles Williams wrote:
There are 3 ways I know of to do something like this:

1. split the workbook into several workbooks, one containing all the "auto"
sheets and then one book for each of the "manual" sheets.
You will need links between the books as appropriate.
Then you can open the "auto" book and the "manual" book you are currently
interested in. Then when you want a different "manual" book you close the
one thats open and open the next one.

This will not work well if the links between the sheets are complex, and you
would need to have Excel in automatic mode so that the open "manual" book
would always calculate.

2. write some VBA that uses the enablecalculation property of the worksheets
to control whether they get automatically recalculated or not. This is not
all that straighforward.

3. use FastExcel mixed calculation mode.

---------------

I've been pondering whether it's worth all the grief to split it up into
separate workbooks. I'm sort of philosophically opposed to that, but it
may be the pragmatic solution.

The VBA approach is what I wondered about. Sounds like one does exist.
I've got a VBA reference book on order so hopefully that'll arrive in
a few days and then I can stare at that approach a bit.

I guess I'll also stare at the spreadsheet some to see whether
suggestions from your web site help in this case. Possibly the one
about adding additional columns to reduce repeated calculations might
impact it significantly enough to be worthwhile.

Thanks Charles...

Bill

--

Dave Peterson

Dave Peterson wrote:
I think this capability was added in xl2002.

But you could use something like:

Option Explicit
Sub TurnOffCalc()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = "sheet1", "sheet3", "sheet5"
wks.EnableCalculation = False
End Select
Next wks

End Sub

To turn calculation off. (and "= True" to toggle it back on).

(Don't trust your output until you turn it back on for all the sheets--which
should recalc the sheets, too.)

-------

I'll tinker with that some. I'm not philosophically opposed to
upgrading my Excel from 97, I just haven't yet hit a situation where I
needed a new feature. Perhaps I've hit it now and need to reconsider.

Thanks Dave...

Bill