Forum Discussion

Mark_Chor's avatar
Mark_Chor
Copper Contributor
Jan 02, 2024
Solved

Convert all sheets from entire workbook from formulae to data format

Hi.  I have a need to convert ALL formulae from an entire Esxel workbook wth 130 sheets and each sheet with up to 3000 rows into data.  I know how to use copy and paste/special to convert data but only do so one sheet at a time.

 

Is there any way to do the entire workbook all at the same time?

 

Thank you

Excel on Mac
Formulas and Functions
  • djclements's avatar
    djclements
    Jan 10, 2024

    Mark_Chor To process 50 worksheets at a time with the code presented by HansVogelaar, use a For Next Loop to loop through a set number of worksheets at a time. For example, if your workbook has 130+ worksheets, you could break it up into 3 groups as follows:

     

    Sub Formulas2ValuesGroup1()
    Dim i As Long
    For i = 1 To 50
        With ThisWorkbook.Worksheets(i).UsedRange
            .Value = .Value
        End With
    Next i
End Sub

Sub Formulas2ValuesGroup2()
    Dim i As Long
    For i = 51 To 100
        With ThisWorkbook.Worksheets(i).UsedRange
            .Value = .Value
        End With
    Next i
End Sub

Sub Formulas2ValuesGroup3()
    Dim i As Long
    For i = 101 To ThisWorkbook.Worksheets.Count
        With ThisWorkbook.Worksheets(i).UsedRange
            .Value = .Value
        End With
    Next i
End Sub

     

    Also worth noting, since you're experiencing an "Out of memory" issue, it's a good idea to close all other applications, as well as any other Excel workbooks, in order to free up as much memory as possible.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jan 09, 2024

    Have you tried first grouping all sheets in the workbook before doing the usual copy/paste values? It might be a challenge to complete the operation memory-wise but worth a try.

    • Mark_Chor's avatar
      Mark_Chor
      Copper Contributor
      Jan 10, 2024
      Patrick2788

      I did try with no success. Using Excel 365 for Mac, and macOS Sonoma 14.1.2 I tried but failed to group all sheets in the workbook as you suggested. In fact I could not even choose 2 sheets prior to copy and paste. On the Net, some contributors did suggest and showed on YouTube how to do it but I suspect it was through using non-Mac Excels. If you can suggest how to do it using Mac Excel I'd be grateful.

      I tried the kind suggestion by HansVogelaar of running the Macro called "Formulas2Values()" on a much smaller document with only several sheets and it worked like magic. As I mentioned in this forum previously, and Hans appeared to agree that trying on my huge file was limited by memory. I might explore chopping up my doc into 3 or 4 , i.e. may be 30 sheets each, and then run the "Formulas2Values()" macro.

      Thank you so much for your kind suggestion. Have a great day. Happy programming.

      Mark Chor
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 02, 2024

    Mark_Chor 

    You can run this macro:

    Sub Formulas2Values()
    Dim ws As Worksheet
    For Each ws In Worksheets
        With ws.UsedRange
            .Value = .Value
        End With
    Next ws
End Sub

    If you store this macro in your personal macro workbook PERSONAL.XLSB, you can run it for any open workbook.

    • Mark_Chor's avatar
      Mark_Chor
      Copper Contributor
      Jan 10, 2024
      I just started exploring VBA for Excel. Question:
      Would you please rewrite your Formulas2Values() macro by using Worksheet(Array) function. That is if it makes sense to you.

      For example: Worksheet(Array("Apple","Mastercard","Microsoft").Select

      I was hoping this would allow me to enter say 50 sheets, use your macro to copy and paste, then run a similar macro for the next 50 sheets to achieve my need.

      I know it is cumbersome and clearly not elegant but hopefully the 50 sheets would be within memory limit. Naturally if it turns out to be still too much I can reduce the # of sheets for each cycle.

      Your comment and assistance would be greatly appreciated.

      Mar
      • djclements's avatar
        djclements
        Bronze Contributor
        Jan 10, 2024

        Mark_Chor To process 50 worksheets at a time with the code presented by HansVogelaar, use a For Next Loop to loop through a set number of worksheets at a time. For example, if your workbook has 130+ worksheets, you could break it up into 3 groups as follows:

         

        Sub Formulas2ValuesGroup1()
    Dim i As Long
    For i = 1 To 50
        With ThisWorkbook.Worksheets(i).UsedRange
            .Value = .Value
        End With
    Next i
End Sub

Sub Formulas2ValuesGroup2()
    Dim i As Long
    For i = 51 To 100
        With ThisWorkbook.Worksheets(i).UsedRange
            .Value = .Value
        End With
    Next i
End Sub

Sub Formulas2ValuesGroup3()
    Dim i As Long
    For i = 101 To ThisWorkbook.Worksheets.Count
        With ThisWorkbook.Worksheets(i).UsedRange
            .Value = .Value
        End With
    Next i
End Sub

         

        Also worth noting, since you're experiencing an "Out of memory" issue, it's a good idea to close all other applications, as well as any other Excel workbooks, in order to free up as much memory as possible.

    • Mark_Chor's avatar
      Mark_Chor
      Copper Contributor
      Jan 08, 2024

      HansVogelaar 

      Hi Hans:

      Many thx for your concise and clear-cut suggestion.  When I used it on a small workbook with 3 sheets, it worked like a charm.  Wow!  Wow! and Wow.

       

      Unfortunately when I ran it on my original file with many more ,about 120, worksheets, Visual Basic showed out of memory msg as shown.

       

      I use a MacBook Pro with:

      32 GB memory,

      macOS: Sonoma 14.1.2

      and Microsoft Excel Version 16.80 with a Microsoft 365 subscription.

       

      I do not have the skill to debug the Visual Basic language.  Will try to see if I can reduce the file size and try again.

       

      Thanks so much for your effort.

       

      Mark

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 08, 2024

        Mark_Chor 

        You could run the following formula on a sheet by sheet basis. Tedious, but perhaps it'll work.

        Sub Formulas2Values1Sheet()
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
End Sub

Resources