Convert all cells contents in the sheet to values

A

An Excel User

I have a spread sheet at work that is linked up to other sheets. We keep the sheets on a shared drive so that everyone can make their updates. The main sheet which I operate is just linked to the others and picks up everybody's updates. Twice a week, I need to email this out to my boss. Is there a general function which converts all cells contents in the sheet to values that I can use before I sent this? I know the Paste Special feature can be used for this, but I have a lot of sheets and I want to get it done with a single keystroke. Isn't there a Save feature that simply writes out a snapshot?
 
S

Stefan Hägglund [MSFT]

Hi!

You can use a macro to change all formulas in all sheets to values.

Here is one example:

Sub KeepValues()
For x = 1 To ActiveWorkbook.Worksheets.Count
Worksheets(x).Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Next x
End Sub


Best regards

Stefan Hägglund
Microsoft
 
F

Frank Kabel

Hi
I would use something like the following

sub change_to_value()
dim wks as worksheet
application.screenupdating=false
for each wks in worksheets
wks.usedrange.value=wks.usedrange.value
next
application.screenupdating=true
end sub
 
Top