Hello Excellers and Welcome to another #macromonday. I want to share with you a Macro that will convert all the cells in a your active workbook to values only. This is the same as using the copy function followed by the Paste Special Values function, but I find this method much quicker and I use it regularly. So, if you use Excel, you’ll love this macro! It converts all of the cells in a worksheet to values. This is really helpful when you need to eliminate any formulas or formatting from your data. Just run the macro, and your worksheet will be clean and ready to use. Give it a try today!
How Does The Macro Work?
The Macro will convert all of the cells in the active worksheet to values.
Preparing To Write The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Hit ALT +F11 or
- Select the Developer Tab | Code Group | Visual Basic.
Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. Well, if you save the macro in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. So, this macro would be useful to reuse in any workbook with multiple Pivot Tables. I will save it in my Personal Macro Workbook.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then please read my additional articles below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Step 1. Insert New Module
So,the first step is to insert a new module into your personal macro workbook. In the Visual Basic Editor hit Insert then Module. Now type Sub and the name of the macro. In this instance the macro has the name ConvertCellsToValues. Note than Excel also inserts the End Sub line of code into the editor. All other lines of code should be written between these two lines.
Sub ConvertCellsToValues()
End Sub
Step 2. Copy All Cells In The Active Worksheet
The next line of code copies all cells in the active worksheet. Simple right?
ActiveSheet.Cells.Copy
Step 3. Paste Cell Values
Now, the copied cells are pasted back into position with the Paste Special command. After this the marcro end with the End Sub line of code that was inserted into the macro at the beginning in Step 1 of the process.
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
Its a really quick and simple way to copy and paste special values in your Excel worksheet.
Want To Copy The Code To Convert Cells To Values?
Sub ConvertCellsToValues()
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
End Sub
If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not book mark it?, Yes it is updated EVERY Monday.
You can also download the corresponding example workbook that contains the Macro code by clicking on the download button below.
How To Excel At Excel – Macro Mondays Blog Posts.
