Excel convert all formulas to values

Objective

  • Convert all formulas to values in the entire workbook with a macro.

How to

  • ALT + F11
  • Menu Insert -> Module
  • Paste in the code below and click Run (F5)
Sub Replace_Formulas()
 Dim wbk As Workbook
 Dim ws As Worksheet
 Dim rng As Range
 Dim cl As Object
 
 Application.Calculation = xlCalculationManual
 Application.ScreenUpdating = False
 On Error Resume Next
 
 Set wbk = ActiveWorkbook
 For Each ws In wbk.Sheets
 Set rng = ws.Range("A1").SpecialCells(xlCellTypeFormulas, 23)
 If Not (rng Is Nothing) Then
 For Each cl In rng
 cl.Value = cl.Value
 Next cl
 End If
 Next ws
 Application.Calculate
 Application.Calculation = xlCalculationAutomatic
 Application.ScreenUpdating = True
 MsgBox "Done"
End Sub