stop calculation on a sheet
 

Hi,
I have a huge Workbook with many sheets doing different reports using
Sumproduct, Match, array, Etc.
The problem is when we try to enter new data, the Workbook recal on each
entries and take a lot of time.

On the "data sheet" I need to keep the cal because I have a match to
retrieve the name of the user using his salesman number. I have also an array
in each row.

I tried adding on the "Reports sheet" the code below, however the worksheet
still do the calculation. Can you help Thanks

This is the code I use in the Code Report sheet.

Private Sub Worksheet_Calculation()
Worksheets.EnableCalculation = False
End Sub

Thanks

stop calculation on a sheet
 

Have you tried

Application.Calculation = xlCalculationManual
and
Application.Calculation = xlCalculationAutomatic

HTH,
Barb Reinhardt

"Mouimet" wrote:

Hi,
I have a huge Workbook with many sheets doing different reports using
Sumproduct, Match, array, Etc.
The problem is when we try to enter new data, the Workbook recal on each
entries and take a lot of time.

On the "data sheet" I need to keep the cal because I have a match to
retrieve the name of the user using his salesman number. I have also an array
in each row.

I tried adding on the "Reports sheet" the code below, however the worksheet
still do the calculation. Can you help Thanks

This is the code I use in the Code Report sheet.

Private Sub Worksheet_Calculation()
Worksheets.EnableCalculation = False
End Sub

Thanks

stop calculation on a sheet
 

Thanks Barb for the quick answer
Yes I just did.
I even close the file and open it again.
I do not know why it doesn't work
Here what I did

Private Sub Worksheet_Calculate()
Application.Calculation = XlCalculationManual
End Sub


"Barb Reinhardt" wrote:

Have you tried

Application.Calculation = xlCalculationManual
and
Application.Calculation = xlCalculationAutomatic

HTH,
Barb Reinhardt

"Mouimet" wrote:

Hi,
I have a huge Workbook with many sheets doing different reports using
Sumproduct, Match, array, Etc.
The problem is when we try to enter new data, the Workbook recal on each
entries and take a lot of time.

On the "data sheet" I need to keep the cal because I have a match to
retrieve the name of the user using his salesman number. I have also an array
in each row.

I tried adding on the "Reports sheet" the code below, however the worksheet
still do the calculation. Can you help Thanks

This is the code I use in the Code Report sheet.

Private Sub Worksheet_Calculation()
Worksheets.EnableCalculation = False
End Sub

Thanks

stop calculation on a sheet
 

Option Explicit
Private Sub Workbook_Open()
Me.Worksheets("Reports").EnableCalculation = False
End Sub

I'd have a macro in a general module that would turn calculation back on:

Option Explicit
Sub TurnCalcBackOn()
Thisworkbook.Worksheets("Reports").EnableCalculati on = True
End Sub

So I could run this when I needed to.

Mouimet wrote:

Hi,
I have a huge Workbook with many sheets doing different reports using
Sumproduct, Match, array, Etc.
The problem is when we try to enter new data, the Workbook recal on each
entries and take a lot of time.

On the "data sheet" I need to keep the cal because I have a match to
retrieve the name of the user using his salesman number. I have also an array
in each row.

I tried adding on the "Reports sheet" the code below, however the worksheet
still do the calculation. Can you help Thanks

This is the code I use in the Code Report sheet.

Private Sub Worksheet_Calculation()
Worksheets.EnableCalculation = False
End Sub

Thanks

--

Dave Peterson