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.
' 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
Filed under: ASAP Utilities, General on September 16th, 2008 by Bastien | 2 Comments »
Tweet
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)
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