Replace formulas with their calculated results in multiple sheets

It's time to update this blog more often. I will do this by writing about questions I answered by email and that might be interesting for others too.

Tom asked: Formula » Convert formulas to their values: I was hoping you could tell me if there is there a way to do this for all the sheets in a workbook instead of one sheet at a time?

At this moment you can only do this with ASAP Utilities one sheet at a time. The utility will replace all cells with formulas in your selection with their calculated results.

If you want to replace all formulas in multiple worksheet with their calculated results, you can use the following macro.
To use it, select the sheets where the formulas need to be changed to their calculated values and then run this utility. To quickly select all sheets in your workbook, you can either use ASAP Utilities:
Select » Select all sheets
Or you can right-click on a sheet-tab and choose "Select All Sheets" in the popup menu.

Sub FormulasToResults()
      ' usage:
      ' 1. select the sheets
      ' 2. run this macro

      Dim ws                                           As Worksheet
      Dim shSelection                                  As Sheets

      Set shSelection = ActiveWindow.SelectedSheets

      If MsgBox("Do you want to replace all formulas with their " & vbNewLine & _
                "calculated results on the selected " & shSelection.Count & _
                " worksheets?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
      Application.ScreenUpdating = False
      For Each ws In ActiveWindow.SelectedSheets
            If ws.Type = xlWorksheet Then
                  ws.Activate
                  ws.UsedRange.Copy
                  ws.UsedRange.PasteSpecial xlPasteValues
                  Application.CutCopyMode = False
            End If
      Next ws
      Application.ScreenUpdating = True
      shSelection.Select
End Sub

2 Responses to “Replace formulas with their calculated results in multiple sheets”

  1. Not sure why this needs a macro? This worked for me in EXCEL 2007:

    — Selected which sheets I wanted
    — Selected all cells (Ctrl-A twice, first time does used range)
    — Copied all cells (Ctrl-C)
    — Paste specials, values (Alt-E,S,V)

  2. Hello Randy,

    Thank you for sharing this!

    I didn't knew it would work that way. I just did a test and that method also works in Excel 2000 and 2002/XP.
    The benefit of a macro is that you can include it in an automated process.

    Kind regards,
    Bastien

Pinterest