Ablity to just recalc one worksheet

[Guest]

is there a way to setup a macro to just recalc one worksheet rather then the
entire project?

 

[Guest]

Not that I'm aware of (someone else may later make me aware of one) -
consider that if there are any off-sheet dependencies, they may need to be
recalculated to make the results on your one sheet accurate.

If constant recalculating is causing a real slowdown, you could turn
automatic calculation off and then manually recalculate when only when you
feel the need.

To set calculation to Manual:
Tools | Options | [Calculation] tab - choose Manual
to calculate manually while the workbook is in use: [F9]

 

[theSquirrel]

In the "ThisWorkbook" module, put enter this code:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.EnableCalculation = False
Next ws
Worksheets("Sheet1").EnableCalculation = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
ws.EnableCalculation = True
Next ws
End Sub

Taht should work to disable the calculations upon opening hte workbook
then reenable them when leaving the applicaiont.

Where it says "Worksheets("Sheet1").EnableCalculation = True" you need
to enter the name of the sheet that you want to enable calculations on.

Hope the helps.

theSquirrel

Not that I'm aware of (someone else may later make me aware of one) -
consider that if there are any off-sheet dependencies, they may need to be
recalculated to make the results on your one sheet accurate.

If constant recalculating is causing a real slowdown, you could turn
automatic calculation off and then manually recalculate when only when you
feel the need.

To set calculation to Manual:
Tools | Options | [Calculation] tab - choose Manual
to calculate manually while the workbook is in use: [F9]

is there a way to setup a macro to just recalc one worksheet rather then the
entire project?- Hide quoted text -- Show quoted text -

 

[NickHK]

Cameron,
Check the Help for the Calculate method.
You will see the objects that it can be applied.
(Hint: The answer to your question is Yes)

NickHK

 

[Guest]

See, I knew someone would make me 'aware'! Thanks for that. Now tucked away
for future use.
Learn something new everyday here - great group effort!

In the "ThisWorkbook" module, put enter this code:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
ws.EnableCalculation = False
Next ws
Worksheets("Sheet1").EnableCalculation = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
ws.EnableCalculation = True
Next ws
End Sub

Taht should work to disable the calculations upon opening hte workbook
then reenable them when leaving the applicaiont.

Where it says "Worksheets("Sheet1").EnableCalculation = True" you need
to enter the name of the sheet that you want to enable calculations on.

Hope the helps.

theSquirrel

Not that I'm aware of (someone else may later make me aware of one) -
consider that if there are any off-sheet dependencies, they may need to be
recalculated to make the results on your one sheet accurate.

If constant recalculating is causing a real slowdown, you could turn
automatic calculation off and then manually recalculate when only when you
feel the need.

To set calculation to Manual:
Tools | Options | [Calculation] tab - choose Manual
to calculate manually while the workbook is in use: [F9]

is there a way to setup a macro to just recalc one worksheet rather then the
entire project?- Hide quoted text -- Show quoted text -