Macro to save all formulas as values

**adrianodl** · 08-22-2012, 11:48 PM

Hi there,

Is there such a macro to save a spreadsheet full of formulas as values instead? I'm automating a process but the tool I'm using reads the actual content of the cell, hence the need run through the spreadsheet and save all formulas as a value.

Any guidance appreciated!

Adri

**MarvinP** · 08-23-2012, 12:17 AM

Hi adrianodl,

There is a tool called PUP 6 that has a feature I think will do your work. Find it at http://spreadsheetpage.com/index.php/pupv6/home
You may have to buy it?
Look at what it does on http://spreadsheetpage.com/index.php/pupv6/utilities

Hope this is what you need.

**jonelamora** · 08-23-2012, 12:33 AM

I found this code over the net. maybe this is what you need. Just make sure to remember that this one will not only work on the active sheet, but on the entire open workbook.

Sub FormulasToValues_EntireWorkbook()
'This Macro will replace all formulas from an entire workbook 
'with their respective values. It will just changes all 
'formulas to values.

WCount = Worksheets.Count
For i = 1 To WCount
If Worksheets(WCount - i + 1).Visible Then
Worksheets(WCount - i + 1).Select
RCount = ActiveCell.SpecialCells(xlLastCell).Row
CCount = ActiveCell.SpecialCells(xlLastCell).Column
For j = 1 To RCount
For k = 1 To CCount
Worksheets(WCount - i + 1).Cells(j, k) = Worksheets(WCount - i + 1).Cells(j, k).Value
Next k
Next j
End If
Next i

End Sub

**HaHoBe** · 08-23-2012, 12:53 AM

Hi, Adri ,

for the active sheet:

Sub ChangeToValues_ActiveSheet()
With ActiveSheet.UsedRange
  .Value = .Value
End With
End Sub

Ciao,
Holger

**MarvinP** · 08-23-2012, 01:11 AM

Now that I see the queston again, I may have given a bad answer.

If you copy an entire sheet and paste over the top of it using "Values Only" it will replace all the formulas with their calculated values. No macros or code needed!!

Is that what you wanted to happen?

**adrianodl** · 08-23-2012, 01:20 AM

Awesome everyone!
Will test and let me know.

Thanks for now!

**rjolly** · 08-23-2012, 02:08 AM

It is as simple as copy / paste as value. I record a macro, selecting the range, select copy from context menu and paste, either in place or on another sheet. You'll want to have a 2nd sheet with the formulas if you copy in place, if you copy to a new sheet, the formulas will be in place for the next time. If you want this to happen on a regular basis, after copy/paste as value, you may insert(in the macro) the number of rows or columns of the range, so the next copy/paste as value will have the space. Your archives continue moving to the right or down so your newest info remains on top, and you can avoid looking for the first empty row or column.