De-Activating all Formulas in a TAB

[RudeBoy]

Is there an easy way to de-activate all formulas in a TAB easily & then re-activate them?

Normally i have to rename out my =IF statements to accomplish this and then rename them back to activate them.

Thanks for your help

 

[StephenCrump]

Can you please explain in more detail what you're trying to do here, and why you can't just set Calculation to manual?

And why do you refer specifically to IF statements, rather than formulae in general?

 

[kvsrinivasamurthy]

Try. To stop auto calculations.
Formulas --> Calculation Options --> Manual
To restore
Formulas --> Calculation Options --> Automatic

 

[RudeBoy]

Can you please explain in more detail what you're trying to do here, and why you can't just set Calculation to manual?

And why do you refer specifically to IF statements, rather than formulae in general?

Years ago a friend if mine helped build a macro to do thousands of calculations. In other tabs i built manual label if statements and made boxes specific to label sizing so once the macro calculations are complete then i can print the results via the label tabs. What happens is if the IF statement are not de-activate by renaming =IF to #IF and i run the Macro. Fields within those other tabs move and get messed up and its a mess to restore the formulas. So the work around i have used for years to deactivate all the =IF statements by renaming them then run the macro and re-activate IF statements and all is perfect.

 

[StephenCrump]

If I understand you correctly, I think this means

- You have a sheet called "Label1", say, with a formula that points to Sheet1!A1, say
- The macro moves Sheet1 around, so that the formula now points to a different cell, Sheet1!B6, say
- After the macro has finished rearranging, the correct reference is again Sheet1!A1
- You need to come in and manually change the reference B6 back to A1.

To me, this suggests that Sheet 1 and/or the macro code needs to be changed so that it doesn't get rearranged.

But if it's easier for you to continue working around, perhaps the following would help?

Sub ConvertToText()

    Dim ws As Worksheet
    
    'Assumes you have three label sheets called Label1, Label2 and Label3
    For Each ws In Worksheets(Array("Label1", "Label2", "Label3"))
        ws.Cells.Replace What:="=", Replacement:="/=", LookAt:=xlPart
   Next ws
  
End Sub
Sub ConvertToFormula()

    Dim ws As Worksheet
    
    For Each ws In Worksheets(Array("Label1", "Label2", "Label3"))
        ws.Cells.Replace What:="/=", Replacement:="=", LookAt:=xlPart
   Next ws
  
End Sub

 

[RudeBoy]

If I understand you correctly, I think this means

- You have a sheet called "Label1", say, with a formula that points to Sheet1!A1, say
- The macro moves Sheet1 around, so that the formula now points to a different cell, Sheet1!B6, say
- After the macro has finished rearranging, the correct reference is again Sheet1!A1
- You need to come in and manually change the reference B6 back to A1.

To me, this suggests that Sheet 1 and/or the macro code needs to be changed so that it doesn't get rearranged.

But if it's easier for you to continue working around, perhaps the following would help?

Sub ConvertToText()

    Dim ws As Worksheet
   
    'Assumes you have three label sheets called Label1, Label2 and Label3
    For Each ws In Worksheets(Array("Label1", "Label2", "Label3"))
        ws.Cells.Replace What:="=", Replacement:="/=", LookAt:=xlPart
   Next ws
 
End Sub
Sub ConvertToFormula()

    Dim ws As Worksheet
   
    For Each ws In Worksheets(Array("Label1", "Label2", "Label3"))
        ws.Cells.Replace What:="/=", Replacement:="=", LookAt:=xlPart
   Next ws
 
End Sub

Is there a Easy way to Create a Macro Called WAXOFF (Renames If statement out) & WAXON Puts IF statements back in action?
These are the Tabs I need to Waxoff & Waxon:

M-Mix-Dbls-1
M-Mix-Dbls-2
M-Mix-Dbls-3-Manual-WRK-NEEDED
M-Mix-Dbls-SORT
M-Mix-Dbls-Final-Pass
M-Mix-Dbls-FINAL
W-Mix-Dbls-1
W-Mix-Dbls-2
W-Mix-Dbls-3-Manual-WRK-NEEDED
W-Mix-Dbls-SORT
W-Mix-Dbls-Final-Pass
W-Mix-Dbls-FINAL
Team-Event-Labels
M-Singles-Labelz
W-Singles-Labelz
Mens-DBLs-Labelz
W-DBLs-Labelz
MSSP-Labelz-GM1
MSSP-Labelz-GM2
MSSP-Labelz-GM3
WSSP-Labelz-GM1
WSSP-Labelz-GM2
WSSP-Labelz-GM3

I think having a Macro to run to rename IF statements out then run my main Macro and then run WaxOn to put IF statements back in action

 

[StephenCrump]

Did you try the code in Post #5?

 

[RudeBoy]

Did you try the code in Post #5?

Do I attach that to each label tab in VBA?

 

[StephenCrump]

Perhaps you could start by testing your first two label worksheets (on a backup copy of your workbook):

Sub ConvertToText()

    Dim ws As Worksheet
        
    For Each ws In Worksheets(Array("M -Mix - Dbls - 1", "M -Mix - Dbls - 2"))
        ws.Cells.Replace What:="=", Replacement:="/=", LookAt:=xlPart
   Next ws
  
End Sub
Sub ConvertToFormula()

    Dim ws As Worksheet
    
    For Each ws In Worksheets(Array("M -Mix - Dbls - 1", "M -Mix - Dbls - 2"))
        ws.Cells.Replace What:="/=", Replacement:="=", LookAt:=xlPart
   Next ws
  
End Sub

 

[RudeBoy]

Perhaps you could start by testing your first two label worksheets (on a backup copy of your workbook):

Sub ConvertToText()

    Dim ws As Worksheet
       
    For Each ws In Worksheets(Array("M -Mix - Dbls - 1", "M -Mix - Dbls - 2"))
        ws.Cells.Replace What:="=", Replacement:="/=", LookAt:=xlPart
   Next ws
 
End Sub
Sub ConvertToFormula()

    Dim ws As Worksheet
   
    For Each ws In Worksheets(Array("M -Mix - Dbls - 1", "M -Mix - Dbls - 2"))
        ws.Cells.Replace What:="/=", Replacement:="=", LookAt:=xlPart
   Next ws
 
End Sub

Tried to run vba from M -Mix - Dbls - 1 and received this error?