22 | 06 | 2017

Excel Save and Recover Formulas using VBA

The code ListAllFormulas below saves all formulas on a sheet to a sheet called FORMULAS. Using RecoverFormulas all formulas from the sheet are recovered again. 

 

Sub ListAllFormulas()
Application.DisplayAlerts = False

Dim shtName
Dim myRng As Range
Dim newRng As Range

Dim oWS As Worksheet
Dim oShtFormulas As Worksheet
Dim oCell As Range
Dim rowi As Integer
    
Set oWS = ActiveWorkbook.Worksheets("SheetWithFormulas")

'Check if Formula Sheet exists
For Each sht In ActiveWorkbook.Worksheets
If sht.Name = "Formulas" Then sht.Delete: Exit For
Next
Set oShtFormulas = ActiveWorkbook.Worksheets.Add()
oShtFormulas.Name = "Formulas"

oShtFormulas.Range("A1").Value = "Formula"
oShtFormulas.Range("B1").Value = "Cell"

rowi = 1
For Each oCell In oWS.Cells.SpecialCells(xlCellTypeFormulas)
    rowi = rowi + 1
    oShtFormulas.Range("A" & rowi).Value = oCell.Formula
     'places the formula minus the '=' sign in column A
    oShtFormulas.Range("B" & rowi).Value = Application.WorksheetFunction.Substitute(oCell.Address, "$", "")
     'places the cell address, minus the "$" signs, containing the formula in column B
Next oCell

oShtFormulas.Activate
         
Application.DisplayAlerts = True
End Sub



Sub RecoverFormulas()
          
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim oWS As Worksheet
Dim oShtFormulas As Worksheet

Set oWS = ActiveWorkbook.Worksheets("SheetWithFormulas")
Set oShtFormulas = ActiveWorkbook.Worksheets("Formulas")

rowi = 1

Do

rowi = rowi + 1 oWS.Range(oShtFormulas.Range("B" & rowi).Value).Formula = oShtFormulas.Range("A" & rowi).Formula Loop Until oShtFormulas.Range("B" & rowi + 1).Value = "" oWS.Activate Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

 
Login

Sign up now and upload your code to the website.

Help us to continue.....
Statistics
Articles View Hits
2284700
Latest Articles