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

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 10, 2024
      Hi.

      Very elegant way to solve my problem. Also appreciated suggestion re: free up memory by close other apps.

      Thx so much. Have a great day.

      Mark
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 10, 2024

    Mark_Chor 

    That is a trivial modification:

    Sub Formulas2Values()
    Dim ws As Worksheet
    For Each ws In Worksheets(Array("Apple", "Mastercard", "Microsoft"))
        With ws.UsedRange
            .Value = .Value
        End With
    Next ws
End Sub
    • Mark_Chor's avatar
      Mark_Chor
      Copper Contributor
      Jan 10, 2024
      Thx very much. Learned a lot from you.

      Pls also see suggestion from djclements to me. Definitely more elegant than what I last suggested. His method is a far better one than mine. For example I don't have to enter names of sheets. 🙂

      Have a good day.

      Mark
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 10, 2024

        Mark_Chor 

        If djclements's reply solved your problem, please mark it as the answer. That will be useful to others who read this topic.

Resources