I have a Macro that runs on Caclulate that looks at a dynamic dataset as well
as a static dataset. If there is a variance between the two datasets, it
will copy the values of the dynamic dataset and paste them into the static
dataset so that there is no more variance.

My problem is that when the workbook I am in is on Auto Calculate it will
exit the subroutine that houses my macro once the data is pasted and go back
to the Workbook_SheetCalculate subroutine and get stuck in a loop because the
variance between the dynamic dataset and the static dataset is never
reconciled. Is there a way I can temprarily bypass the autocalculate
functionality to keep this from happening?

Thanks,
Chad

Try this....

Dim strOrigCalcStatus As String

'save current calculation setting
Select Case Application.Calculation
Case xlCalculationAutomatic
strOrigCalcStatus = "Automatic"
Case xlCalculationManual
strOrigCalcStatus = "Manual"
Case xlCalculationSemiautomatic
strOrigCalcStatus = "SemiAutomatic"
Case Else
strOrigCalcStatus = "Automatic"
End Select

'set workbook to manual
Application.Calculation = xlCalculationManual

'PUT YOUR CODE HERE

're-set workbook to original calculation status
Select Case strOrigCalcStatus
Case "Automatic"
Application.Calculation = xlCalculationAutomatic
Case "Manual"
Application.Calculation = xlCalculationManual
Case "SemiAutomatic"
Application.Calculation = xlCalculationSemiautomatic
Case Else
Application.Calculation = xlCalculationAutomatic
End Select

--
HTH,
Gary Brown
(E-Mail Removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Chad" wrote:

> I have a Macro that runs on Caclulate that looks at a dynamic dataset as well
> as a static dataset. If there is a variance between the two datasets, it
> will copy the values of the dynamic dataset and paste them into the static
> dataset so that there is no more variance.
>
> My problem is that when the workbook I am in is on Auto Calculate it will
> exit the subroutine that houses my macro once the data is pasted and go back
> to the Workbook_SheetCalculate subroutine and get stuck in a loop because the
> variance between the dynamic dataset and the static dataset is never
> reconciled. Is there a way I can temprarily bypass the autocalculate
> functionality to keep this from happening?
>
> Thanks,
> Chad

Gary,
Thanks for your quick response. I tried something like that earlier
with my code but I am still running into a problem...After I reset the
Applications Calculation status to its original status (which in this case is
Automatic) the code goes right to the Workbook_SheetCalculate subroutine
again resulting in an unending loop. If you can think of anything else that
would help me out I would really appreciate it.

Thanks,
Chad

"Gary Brown" wrote:

> Try this....
>
> Dim strOrigCalcStatus As String
>
> 'save current calculation setting
> Select Case Application.Calculation
> Case xlCalculationAutomatic
> strOrigCalcStatus = "Automatic"
> Case xlCalculationManual
> strOrigCalcStatus = "Manual"
> Case xlCalculationSemiautomatic
> strOrigCalcStatus = "SemiAutomatic"
> Case Else
> strOrigCalcStatus = "Automatic"
> End Select
>
> 'set workbook to manual
> Application.Calculation = xlCalculationManual
>
> 'PUT YOUR CODE HERE
>
> 're-set workbook to original calculation status
> Select Case strOrigCalcStatus
> Case "Automatic"
> Application.Calculation = xlCalculationAutomatic
> Case "Manual"
> Application.Calculation = xlCalculationManual
> Case "SemiAutomatic"
> Application.Calculation = xlCalculationSemiautomatic
> Case Else
> Application.Calculation = xlCalculationAutomatic
> End Select
>
> --
> HTH,
> Gary Brown
> (E-Mail Removed)
> If this post was helpful to you, please select ''YES'' at the bottom of the
> post.
>
>
>
> "Chad" wrote:
>
> > I have a Macro that runs on Caclulate that looks at a dynamic dataset as well
> > as a static dataset. If there is a variance between the two datasets, it
> > will copy the values of the dynamic dataset and paste them into the static
> > dataset so that there is no more variance.
> >
> > My problem is that when the workbook I am in is on Auto Calculate it will
> > exit the subroutine that houses my macro once the data is pasted and go back
> > to the Workbook_SheetCalculate subroutine and get stuck in a loop because the
> > variance between the dynamic dataset and the static dataset is never
> > reconciled. Is there a way I can temprarily bypass the autocalculate
> > functionality to keep this from happening?
> >
> > Thanks,
> > Chad

How about something like a global Booleen variable like 'blnContinue'. Work
that into your macro with the macro in the Workbook_SheetCalculate procedure
using an IF statement to test for the value of blnContinue prior to running
the macro.

--
HTH,
Gary Brown
(E-Mail Removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Chad" wrote:

> Gary,
> Thanks for your quick response. I tried something like that earlier
> with my code but I am still running into a problem...After I reset the
> Applications Calculation status to its original status (which in this case is
> Automatic) the code goes right to the Workbook_SheetCalculate subroutine
> again resulting in an unending loop. If you can think of anything else that
> would help me out I would really appreciate it.
>
> Thanks,
> Chad
>
> "Gary Brown" wrote:
>
> > Try this....
> >
> > Dim strOrigCalcStatus As String
> >
> > 'save current calculation setting
> > Select Case Application.Calculation
> > Case xlCalculationAutomatic
> > strOrigCalcStatus = "Automatic"
> > Case xlCalculationManual
> > strOrigCalcStatus = "Manual"
> > Case xlCalculationSemiautomatic
> > strOrigCalcStatus = "SemiAutomatic"
> > Case Else
> > strOrigCalcStatus = "Automatic"
> > End Select
> >
> > 'set workbook to manual
> > Application.Calculation = xlCalculationManual
> >
> > 'PUT YOUR CODE HERE
> >
> > 're-set workbook to original calculation status
> > Select Case strOrigCalcStatus
> > Case "Automatic"
> > Application.Calculation = xlCalculationAutomatic
> > Case "Manual"
> > Application.Calculation = xlCalculationManual
> > Case "SemiAutomatic"
> > Application.Calculation = xlCalculationSemiautomatic
> > Case Else
> > Application.Calculation = xlCalculationAutomatic
> > End Select
> >
> > --
> > HTH,
> > Gary Brown
> > (E-Mail Removed)
> > If this post was helpful to you, please select ''YES'' at the bottom of the
> > post.
> >
> >
> >
> > "Chad" wrote:
> >
> > > I have a Macro that runs on Caclulate that looks at a dynamic dataset as well
> > > as a static dataset. If there is a variance between the two datasets, it
> > > will copy the values of the dynamic dataset and paste them into the static
> > > dataset so that there is no more variance.
> > >
> > > My problem is that when the workbook I am in is on Auto Calculate it will
> > > exit the subroutine that houses my macro once the data is pasted and go back
> > > to the Workbook_SheetCalculate subroutine and get stuck in a loop because the
> > > variance between the dynamic dataset and the static dataset is never
> > > reconciled. Is there a way I can temprarily bypass the autocalculate
> > > functionality to keep this from happening?
> > >
> > > Thanks,
> > > Chad