Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    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.
    Sub All_Macros(Optional control As Variant)

  • #2
    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

    Comment


    • #3
      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
      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!
      Sub All_Macros(Optional control As Variant)

      Comment


      • #4
        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

        Comment


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

          Originally posted by 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
          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.
          Sub All_Macros(Optional control As Variant)

          Comment


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

            Comment


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

              Originally posted by 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?
              Sub All_Macros(Optional control As Variant)

              Comment


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

                Comment


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

                  Originally posted by 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.
                  Last edited by Aaron Blood; August 24th, 2006, 06:27.
                  Sub All_Macros(Optional control As Variant)

                  Comment


                  • #10
                    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

                    Comment


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

                      Comment


                      • #12
                        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

                        Originally posted by Yajurito View Post
                        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

                        Comment


                        • #13
                          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!
                          _______________________________________________

                          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

                          _______________________________________________

                          Comment


                          • #14
                            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
                            I would be interested in how this method compares to copy and paste.

                            Comment


                            • #15


                              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

                              Comment

                              Working...
                              X