Do not calculate or update formula's during macro

  • Thread starter Thread starter Jules
  • Start date Start date
J

Jules

Is there so code to keep formulas in a spreadsheet from updating? I have a
macro that changes sheet names and location of some data but I do not want
the formulas referring to those sheets to update.

In other words, my formula may read ='Section 2'!A2-'Section 3'!A2

Part of my macro is to rename my sheets so 'Section 3" becomes "Section 2"
and "Section 2" becomes "Section 1".

After my macro, the formula ends up reading ='Section 1'!A2-'Section 2'!A2

I want to avoid that happening. I don't want my formula to know that my
sheets renamed.

Any suggestions?

Thanks!
Jules
 
If you want to stop your formulas from automatically calculating you can use
this code.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation =xlCalculationAutomatic

But this will not stop the other issue you are having. Having your formulas
automatically updated when a sheet is renamed is a big convience that Excel
offers. I would suggest adding a sheet to your workbook in your macro. I
don't know any details of your application or macro so I can't post any code
or give detailed suggestions. If you need help post your macro and we can
see if there is a logical way around your issue. Hope this helps! If so,
let me know, click "YES" below.
 
I had a similar problem. First freeze the formulas and then un-freeze them.

So first call:

Sub freezeUm()
q = Chr(39)
eq = "="
For Each r In ActiveSheet.UsedRange
If r.HasFormula Then
r.Value = q & r.Formula
End If
Next
End Sub

and after you do your thing, call:

Sub unfreezeUm()
eq = "="
For Each r In ActiveSheet.UsedRange
If Not r.HasFormula And Left(r.Value, 1) = eq Then
r.Formula = r.Value
End If
Next
End Sub
 
"I see" said the blind man. Clever piece of code there. Never would have
thought of that.
 
My hero! Awesome. Thanks!!

Gary''s Student said:
I had a similar problem. First freeze the formulas and then un-freeze them.

So first call:

Sub freezeUm()
q = Chr(39)
eq = "="
For Each r In ActiveSheet.UsedRange
If r.HasFormula Then
r.Value = q & r.Formula
End If
Next
End Sub

and after you do your thing, call:

Sub unfreezeUm()
eq = "="
For Each r In ActiveSheet.UsedRange
If Not r.HasFormula And Left(r.Value, 1) = eq Then
r.Formula = r.Value
End If
Next
End Sub
Click to expand...
 
Ok...still my hero! But I can't get this to work quite right. How did you
define your variables to make this work?

Thanks!
 
Ok...still my hero! But I can't get this to work quite right. How did you
define your variables to make this work?

Thanks!
 
Back
Top