Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Stop calculation on one specific sheets only
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Stop calculation on one specific sheets only
You were close!
Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.Calculation = xlCalculationManual End Sub HTH, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Mouimet" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Stop calculation on one specific sheets only
Did the other suggestion work?
Mouimet wrote: 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. -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Stop calculation on one specific sheets only
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 --------------- Jacob Skaria "Mouimet" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Stop calculation on one specific sheets only
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" wrote: 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 --------------- Jacob Skaria "Mouimet" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Stop calculation on one specific sheets only
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" wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Stop calculation on one specific sheets only
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 --------------- Jacob Skaria "Mouimet" wrote: 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" wrote: 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 --------------- Jacob Skaria "Mouimet" wrote: 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Stop calculation on one specific sheets only
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 "Mouimet" wrote in message ... 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
specific totals from sheets represented on other sheets | Excel Discussion (Misc queries) | |||
How to stop in the middle of re-calculation for all cells? | Excel Discussion (Misc queries) | |||
How to stop IRR calculation at a particular year... | Excel Discussion (Misc queries) | |||
Stop a calculation in progress | Excel Discussion (Misc queries) | |||
Stop an in-process subtotals calculation | Excel Worksheet Functions |