Hi, Excellers welcome to another blog post in the #macromondays series. Today, let’ s look at saving a tonne of time. Sounds good right?. We will write an Excel VBA macro which will convert all formulas into values in your Excel worksheet. In some situations such as using a lot of heavy volatile functions, or you may need to send a workbook with only the results of calculations rather than the full Excel model, then this macro is a quick and easy way to convert those formulas to values.
This macro will convert ALL of the formulas in an Excel worksheet to values with the push of a button or click of a mouse.
What Does The Macro Do?
This macro will convert all formulas in an active worksheet to values. Really quickly!.
How Does The Macro Work?
The Macro copies the active cells in the active worksheet and pastes the content back as a value only. This is a rough and ready piece of code but it works….quick.
Step 1. Open the Visual Basic Editor. You can do this by either selecting the Developer Tab | Code | Visual Basic. Alternatively, you can hit ALT+F11 as a shortcut to open the Visual Basic Editor window.
Step 2. Decide where you want to save your Excel code. If we want to use this code only in this workbook then we need to insert a new module into this workbook. If we want to use this code in ANY workbook then we can create a module in the Personal Macro Workbook. I invite you to read my blog posts relating to the Personal Macro Workbook below. Saving it in this specific workbook makes your code available to any Excel workbook you use.
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Step 3. We select all of the cells in all of our worksheets in our workbook. (the Worksheets. Select coding will select all worksheets in the current workbook)
Step 4. Excel uses the PasteSpecial Paste:=xlPasteValues command to paste the value only back into the cells of the worksheets.
Step 5. Test your Macro!.
That is all there is to this piece of code. Save this in your Personal Macro Workbook and it will be to hand if you need to convert ALL formulas in ALL worksheet in your current workbook. A quick rough and ready Excel VBA Macro.
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Need Help With An Excel Problem?.
I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.
