So I have a very important Excel workbook and I need the original formula protected at all costs. So I want a process that runs through all of the worksheets in the Excel workbook and converts everything from formulas to values. This way none of the mechanics can be messed with in any way. This is a bit like sending out a read only version of your work. Or converting the whole thing to PDF. There are many ways to skin the preverbal cat as it were.
Option Explicit
Sub ConverttoVals()
Dim sh As Worksheet
For Each sh In Sheets
sh.UsedRange.Copy
sh.UsedRange.PasteSpecial xlPasteValues
Next sh
Application.CutCopyMode = 0
End Sub
The For Each loop covers off all the worksheets in the workbook and the used range covers off only the used data in the workbook. After this the paste special xl values takes over to finish the job.
The process runs reasonably quickly. It will clear the file. You want to make sure that you save the file as a different version and please have a back up just in case you accidently save the file.
Enjoy and I hope it has been useful.