Forum Discussion

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

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

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

Bronze Contributor

Jan 10, 2024

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

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