How to Stop calculation on one specific sheets only

[Mouimet]

I ask this before, however the solution I received didn't work.

I have a big Workbook with many criteria using "SUMPRODUCT" formula .
I need to remove the auto calculation on one of the sheet "Total" because
each time I add something on the Data sheet, Excel recalculate and take more
than 30 seconds.
I need to keep the calculation on the Data sheet because I have some Vlookup
formulas to get names when the user entered the ID numbers.

I tried different VBA like -------------
Private Sub Worksheets_Calculate()
Worksheets("Total").EnableCalculation =false
End Sub
Doesn't work at all. At each Data entries Excel recalculate

Another one------------
Application.Calculation = xlCalculationManual

------
This one stop also my others worksheets to calculate and my others open
Workbook. This is not what I need.

If you have another option, please tell me.
Thanks everyone.

 

[ryguy7272]

You were close!

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
End Sub



HTH,
Ryan--

 

[Dave Peterson]

Did the other suggestion work?

I ask this before, however the solution I received didn't work.

I have a big Workbook with many criteria using "SUMPRODUCT" formula .
I need to remove the auto calculation on one of the sheet "Total" because
each time I add something on the Data sheet, Excel recalculate and take more
than 30 seconds.
I need to keep the calculation on the Data sheet because I have some Vlookup
formulas to get names when the user entered the ID numbers.

I tried different VBA like -------------
Private Sub Worksheets_Calculate()
Worksheets("Total").EnableCalculation =false
End Sub
Doesn't work at all. At each Data entries Excel recalculate

Another one------------
Application.Calculation = xlCalculationManual

------
This one stop also my others worksheets to calculate and my others open
Workbook. This is not what I need.

If you have another option, please tell me.
Thanks everyone.

 

[Jacob Skaria]

If you dont have formulas in Total sheets referring to other sheets....

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Total" Then
Application.Calculation = xlCalculationManual
Else
Application.Calculation = xlCalculationAutomatic
End If
End Sub

If this post helps click Yes

 

[Mouimet]

Hi,
Houston! we have a problem. Both solutions doesn't work
I have a hard time with this workbook

ryguy; I add your lines in the "Total" sheets code.
Did nothing even if I close and reopen the file.
On each entries I see Excel recalculating.

Jacob; Yes the total sheets is refering to the "Data" sheets
I tried anyway your formula and I get the same result.

=========
I will try to explain with examples, maybe this can help

I will take 3 sheets only to explain.
Sheets: Data (all entries will be done here)
Sheets: Totals (Some total reportings using sumproduct because of different
criterias)
Sheets: Names ( list of names and user #)

In data sheets we enter User# and a vlookup formula get the name related to
the user# from the sheet "Names".
Then we enter different date and numbers related to this user.
This sheet need to keep the auto calculation on because of the vlookup.

On sheets "Total" I retrieve the total amount base on 3 differents criterias
using the Sumproduct formula.

This is the sheet I have a problem with calculation. Because each time
I entered a new user# in the Data sheets Excel recalculate all sheets before
showing me the user name.
I remove completly all formulas on the sheet "Total" and everything work fast.
When I entered a new user# I get the name in a second.

Because my problem was the "sumproduct" I think my solution will be
inactivating auto calculation on this sheet only.

I do not know why your solutions like this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
End Sub

Did not work and It should. Excel recalculate on each entries and take time
to show the user name on my Data sheet.

My last solution:
I have a last solution however I dont like this one. If I create a new
workbook for the Report "Total" using links to Data Workbook.
The problem is if we need to look the report we will need to open another
worksheets and said yes for the update.

Hope this can help finding a solution.

 

[Mouimet]

Hi,
Houston! we have a problem. Both solutions doesn't work
I have a hard time with this workbook

ryguy; I add your lines in the "Total" sheets code.
Did nothing even if I close and reopen the file.
On each entries I see Excel recalculating.

Jacob; Yes the total sheets is refering to the "Data" sheets
I tried anyway your formula and I get the same result.

=========
I will try to explain with examples, maybe this can help

I will take 3 sheets only to explain.
Sheets: Data (all entries will be done here)
Sheets: Totals (Some total reportings using sumproduct because of different
criterias)
Sheets: Names ( list of names and user #)

In data sheets we enter User# and a vlookup formula get the name related to
the user# from the sheet "Names".
Then we enter different date and numbers related to this user.
This sheet need to keep the auto calculation on because of the vlookup.

On sheets "Total" I retrieve the total amount base on 3 differents criterias
using the Sumproduct formula.

This is the sheet I have a problem with calculation. Because each time
I entered a new user# in the Data sheets Excel recalculate all sheets before
showing me the user name.
I remove completly all formulas on the sheet "Total" and everything work fast.
When I entered a new user# I get the name in a second.

Because my problem was the "sumproduct" I think my solution will be
inactivating auto calculation on this sheet only.

I do not know why your solutions like this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
End Sub

Did not work and It should. Excel recalculate on each entries and take time
to show the user name on my Data sheet.

My last solution:
I have a last solution however I dont like this one. If I create a new
workbook for the Report "Total" using links to Data Workbook.
The problem is if we need to look the report we will need to open another
worksheets and said yes for the update.

Hope this can help finding a solution.

 

[Jacob Skaria]

Review your formulas; and look at the range you have mentioned for
SUMPRODUCT(). Are you referring to the whole column within your formulas. If
so reduce that to a reasonable range.

If this post helps click Yes

 

[Charles Williams]

Ussetting calculation to Manual won't work because this affects all
worksheets in all open workbooks.

A solution would be to create 2 VBA macros in a General Module

Sub DisableTotal()
Worksheets("Total").EnableCalculation=False
end Sub

Sub EnableTotal()
Worksheets("Total").EnableCalculation=True
End Sub

Then running DisableTotal will switch off automatic and manual calculation
for the Total sheet, and EnableTotal will switch it back on.

Note that you will have to run DisableTotal each time you open the workbook,
and the Total worksheet will probably be calculated each time you open the
workbook.

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