Forum Discussion
Convert all sheets from entire workbook from formulae to data format
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 SubAlso 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.
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 SubIf you store this macro in your personal macro workbook PERSONAL.XLSB, you can run it for any open workbook.
- 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.
MarMark_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 SubAlso 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.
- 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
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- 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
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
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- Hi Hans
Many thanks again.
In my humble opinion, the clicks needed to run this macro are likely similar to that of Excel task: ChooseAll/Copy/PasteSpecial. Unless of course the user is able to assign a combo-shortcut series of keys such as Control-T.
The 2 macros you suggested are simple, elegant, logical. I thank you heartedly. Who knows, I may study Visual Basic !! just so my life of using Excel is more delightful.
Have a great day. And a (belated) Happy New Year.
Mark
