Convert Formulas to Values Instantly... For the entire workbook!

Aaron Blood · Aug 12th 2005

This is the fastest, most efficient way I could come up with to convert every formula in a workbook to values.

Interesting in that it's processed with a single action regardless of the number of sheets in the workbook. Typically people try to do this by stepping thru the sheets; it's not as efficient. And there's a difference between Sheets.Select and Worksheets.Select, be careful not to change that.

Code

Sub Formula_Zapper()
    Worksheets.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Select
    Application.CutCopyMode = False
End Sub

...of course once you understand the macro, you may realize it can be done very quickly manually as well. Which is also a good thing to know. :fyi:

Yajurito · Aug 17th 2006

Re: Convert Formulas to Values Instantly... For the entire workbook!

Hi,

Thank you Aaron because I use this bit of code all the time to submit portions of a larger spreadsheet to clients or suppliers. I have found, however that the code you listed does not work when there are hidden sheets in the workbook, so I have made this slight modification so that it will work when sheets are hidden. Hope somebody out there finds it useful as well.

Cheers,

Edgar

Code

Sub ConvertAllToValues()
'
'Originally Adapted from OZgrid.com
'
Dim OldSelection As Range
Dim HiddenSheets() As Boolean
Dim Goahead As Integer, n As Integer, i As Integer
Goahead = MsgBox("This will irreversibly convert all formulas in the workbook to values. Continue?", vbOKCancel, "Confirm conversion to values only")
If Goahead = vbOK Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


    n = Sheets.Count
    ReDim HiddenSheets(1 To n) As Boolean
    
    For i = 1 To n
        If Sheets(i).Visible = False Then HiddenSheets(i) = True
        Sheets(i).Visible = True
    Next
        
    Set OldSelection = Selection.Cells
    Worksheets.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    
    Cells(OldSelection.Row, OldSelection.Column).Select
    Sheets(OldSelection.Worksheet.Name).Select
    
    Application.CutCopyMode = False
    
    For i = 1 To n
        Sheets(i).Visible = Not HiddenSheets(i)
    Next
    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Display More

Aaron Blood · Aug 18th 2006

Re: Convert Formulas to Values Instantly... For the entire workbook!

If you need it to deal with hidden sheets you could modify my code as follows:

Code

Option Explicit


Sub Formula_Zapper_MkII()
    
    Dim sh As Worksheet, HidShts As New Collection
    For Each sh In ActiveWorkbook.Worksheets
        If Not sh.Visible Then
            HidShts.Add sh
            sh.Visible = xlSheetVisible
        End If
    Next sh
    
    Worksheets.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Select
    Application.CutCopyMode = False
    
    For Each sh In HidShts
'        sh.Delete
        sh.Visible = xlSheetHidden
    Next sh
    
End Sub

Display More

Notice this way you have the option of deleting or rehiding the hidden sheets. If they're hidden, probably no need to include em. ...and still lightning fast!

Dave Hawley · Aug 18th 2006

Re: Convert Formulas to Values Instantly... For the entire workbook!

Aaron, I often use the code below which seems quick. How does it compare to yours?

Code

Sub AllValues()
Dim wSh As Worksheet
    For Each wSh In ActiveWorkbook.Worksheets
         With wSh.UsedRange
             .Copy
             .PasteSpecial xlPasteValues
          End With
    Next wSh
    
    Application.CutCopyMode = False
End Sub

Display More

Aaron Blood · Aug 20th 2006

Re: Convert Formulas to Values Instantly... For the entire workbook!

Quote from Dave Hawley

Aaron, I often use the code below which seems quick. How does it compare to yours?

Code

Sub AllValues()
Dim wSh As Worksheet
    For Each wSh In ActiveWorkbook.Worksheets
         With wSh.UsedRange
             .Copy
             .PasteSpecial xlPasteValues
          End With
    Next wSh
    
    Application.CutCopyMode = False
End Sub

Display More

I believe the only difference is that the quoted code would process n copy/paste actions dependent on the number of sheets in the workbook.

In my code I array select all sheets and process a single copy/pastevalue action. It makes converting even large complex workbooks nearly instantaneous because there are no recalc pauses. Granted, I s'pose you could turn calcs off momentarily...

The performance gain is probably not that big of a deal for an app such as this. The other methods, I'm sure, get the job done in a reasonable amount of time.

Dave Hawley · Aug 20th 2006

Re: Convert Formulas to Values Instantly... For the entire workbook!

I was just curious how using Cells would compare to UsedRange, which is main the difference.

Aaron Blood · Aug 21st 2006

Re: Convert Formulas to Values Instantly... For the entire workbook!

Quote from Dave Hawley

I was just curious how using Cells would compare to UsedRange, which is main the difference.

That code has to step thru the usedranges and process each sheet individually no?

Dave Hawley · Aug 22nd 2006

Re: Convert Formulas to Values Instantly... For the entire workbook!

Of course. My point is that the UsedRange will likely be 1000's of times smaller than all 16 million + Cells on each Worksheet. I was just curious if you had compared you method with this, that's all.

Aaron Blood · Aug 23rd 2006

Re: Convert Formulas to Values Instantly... For the entire workbook!

Quote from Dave Hawley

Of course. My point is that the UsedRange will likely be 1000's of times smaller than all 16 million + Cells on each Worksheet. I was just curious if you had compared you method with this, that's all.

Trust me... it's faster than stepping. You'll find that cell count (in this case) isn't an issue. It's more in avoiding the recalcs.

Dave Hawley · Aug 24th 2006

Re: Convert Formulas to Values Instantly... For the entire workbook!

Of course I trust you...that's why I asked and didn't test myself

sagearbor · Dec 3rd 2010

Re: Convert Formulas to Values Instantly... For the entire workbook!

This does not appear to work for very large excels. I got the error
"Excel cannot complete this task with available resources. Choose less data or close other applications". FYI the file size was 98MB.

eric.hitchman · Nov 13th 2013

Re: Convert Formulas to Values Instantly... For the entire workbook!

Thak you very much for this.

This may be a bit much to ask, but you said you had used this to send to clients and or suppliers, so thought you may have found a solution

What might you add to the code to format any old pivot talbes you might have had in the worksheet. As you well know, the paste values function works great for most data, but pivots don't enjoy being pasted all too much.

Any thoughts? Just trying to convert some large files for client use... unfortunately they love large reports, but can't handle receiving them via any reasonable means.

Thanks for your help :D, the VB update for hidden sheets helped much.

-Eric

Quote from Yajurito;287124

Hi,

Thank you Aaron because I use this bit of code all the time to submit portions of a larger spreadsheet to clients or suppliers. I have found, however that the code you listed does not work when there are hidden sheets in the workbook, so I have made this slight modification so that it will work when sheets are hidden. Hope somebody out there finds it useful as well.

Cheers,

Edgar

Code

Sub ConvertAllToValues()
'
'Originally Adapted from OZgrid.com
'
Dim OldSelection As Range
Dim HiddenSheets() As Boolean
Dim Goahead As Integer, n As Integer, i As Integer
Goahead = MsgBox("This will irreversibly convert all formulas in the workbook to values. Continue?", vbOKCancel, "Confirm conversion to values only")
If Goahead = vbOK Then
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


    n = Sheets.Count
    ReDim HiddenSheets(1 To n) As Boolean
    
    For i = 1 To n
        If Sheets(i).Visible = False Then HiddenSheets(i) = True
        Sheets(i).Visible = True
    Next
        
    Set OldSelection = Selection.Cells
    Worksheets.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    
    Cells(OldSelection.Row, OldSelection.Column).Select
    Sheets(OldSelection.Worksheet.Name).Select
    
    Application.CutCopyMode = False
    
    For i = 1 To n
        Sheets(i).Visible = Not HiddenSheets(i)
    Next
    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

Display More

**Ger Plante** · Nov 13th 2013

Re: Convert Formulas to Values Instantly... For the entire workbook!

Hi Eric - this forum is not for asking questions... please post in the main excel help forum and post a link back to this thread if you need to . Thanks and welcome to the forum!

CoolBlue · Nov 23rd 2013

Re: Convert Formulas to Values Instantly... For the entire workbook!

Hi, just thought I would offer this...

Code

Public Sub rangeToValues()
Dim r As Range, varR As Variant
Dim calcState As Long
Set r = Selection


    With Application
        .screenUpdating = False
        .EnableEvents = False
        calcState = .Calculation
        .Calculation = xlCalculationManual
    End With
    
    varR = r.Value2
    r = varR
    
    With Application
        .screenUpdating = True
        .EnableEvents = True
        .Calculation = calcState
    End With
End Sub

Display More

I would be interested in how this method compares to copy and paste.

SHADABSAYED · Sep 19th 2017

Re: Convert Formulas to Values Instantly... For the entire workbook!

Hi Edgar / Aaron,

Can you please make 2 vb code to convert all the linked formula to value embedded within the formula. And the next to convert to formula again back to normal with linked path. Like pack unpack option.

I make report in excel file which is linked to various other excel file and then I have send this report to management but I don't want this to paste special everytime. I just want to pack the formula and then unpack it when I need to work on the same file.

I hope you understand my need.

Appreciate your help to cross this bridge.

Regards

Shadab

**Ger Plante** · Sep 19th 2017

Re: Convert Formulas to Values Instantly... For the entire workbook!

Hi SHADABSAYED - welcome to the forum. This is no questions forum. Please create a thread in the free Excel help forum and link to this thread if you wish.

Thanks,
Ger

cell_caht · Sep 29th 2022

Quote from Aaron Blood

Re: Convert Formulas to Values Instantly... For the entire workbook!

If you need it to deal with hidden sheets you could modify my code as follows:

Code

Option Explicit


Sub Formula_Zapper_MkII()
    
    Dim sh As Worksheet, HidShts As New Collection
    For Each sh In ActiveWorkbook.Worksheets
        If Not sh.Visible Then
            HidShts.Add sh
            sh.Visible = xlSheetVisible
        End If
    Next sh
    
    Worksheets.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Select
    Application.CutCopyMode = False
    
    For Each sh In HidShts
'        sh.Delete
        sh.Visible = xlSheetHidden
    Next sh
    
End Sub

Display More

Notice this way you have the option of deleting or rehiding the hidden sheets. If they're hidden, probably no need to include em. ...and still lightning fast!

Display More

Hi,

The code written above is very useful. However, my need is a little different. My request is for "certain column range" to be converted to value on all pages. For example, only columns A:F are converted to values, other fields remain with formulas. I would be grateful if you could help with this.

**alansidman** · Sep 29th 2022

cell_caht

Please do not highjack other posters threads. Open your own new thread and reference this one if necessary in your own thread. You will have better success with an original thread. Please review the forum rules you agreed to abide by when you joined.

Convert Formulas to Values Instantly... For the entire workbook!

alansidman Sep 29th 2022

Participate now!

Share

Users Viewing This Thread