VBA Quick way to convert whole sheet to values?

[LNG2013]

Is there a quick way to change a whole sheet into just it's values, so it's values after the formulas?

 

[Rick Rothstein]

Go to the worksheet you want to convert and, with that sheet active, go into the VB editor (CTRL+F11), then find the Immediate Window (CTRL+G if it is not already showing) and type this into it...

ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

and finish your typing off with a press of the Enter key. That's it... go back to the worksheet and you will find all formulas have been replaced by the data they were displaying.

 

[LNG2013]

Wow dude fantastic that worked like a charm!!!!

 

[Rick Rothstein]

Wow dude fantastic that worked like a charm!!!!

There is a manual way to do this also... select all the cells on the worksheet (click the empty cell where the row headers meet the column headers), press CTRL+C, then right-click the selection and select "Paste Special..." from the popup menu that appears, select the Values option button on the dialog box that appears and click OK. Press escape to get rid of the "marching ants" and click anywhere to get rid of the selection. At this point, all your formulas have been converted to their displayed values.