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

  • 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
    1. Sub Formula_Zapper()
    2. Worksheets.Select
    3. Cells.Select
    4. Selection.Copy
    5. Selection.PasteSpecial Paste:=xlPasteValues
    6. ActiveSheet.Select
    7. Application.CutCopyMode = False
    8. 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:

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • 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

  • 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:



    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!

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • 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?

  • 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?


    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.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • 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?

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • 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.

  • 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.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • 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.

  • 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


  • 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! :)

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________

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


    Hi, just thought I would offer this...



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

  • 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

  • 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

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________