Convert all formulas to values

[Henry]

I have a workbook in Excel 2002 that uses an add-in
(called F9) to access a DOS database (ACCPAC Plus data).
The workbook pulls data out of the database and uses it
to create monthly Financial Statements.

I would like to save the workbook each month, converting
all formulas to values so that they won't be accidentally
updated in the future. I thought I could break the links
under the Edit|Links command, but that's greyed out
because all the links point to the DOS database, not to
another spreadsheet.

I could manually do a Copy and Paste Special, converting
to values, but my workbook has 27 worksheets in it, so
it's somewhat time consuming and the potential for error
exists.

Is there any way to do this with one command?

 

[Gord Dibben]

Henry

Sub Copy_Paste_Special()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks _ :=False, Transpose:=False
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Next ws
''ActiveWorkbook.Close SaveChanges:=True''include this line to save and close
End Sub

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[Tom Ogilvy]

this would probably be faster:

Sub convert_to_values()
'Removes ALL formulas and replaces them with valves,
'for each sheet in your workbook
Application.ScreenUpdating = False
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
sht.Cells.copy
sht.Cells.PasteSpecial xlValues
Next Sht
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

No reason not to just copy everything and do a pastespecial values.

or

Sub convert_to_values()
'Removes ALL formulas and replaces them with valves,
'for each sheet in your workbook
Application.ScreenUpdating = False
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
sht.UsedRange.copy
sht.UsedRange.PasteSpecial xlValues
Next Sht
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

or

Sub convert_to_values()
'Removes ALL formulas and replaces them with valves,
'for each sheet in your workbook
Application.ScreenUpdating = False
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
sht.UsedRange.Formula = Sht.UsedRange.Value
Next Sht
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Don Guillett]

try this. Tested in xl2002

Sub MakeValues()
Sheets.Select
With Selection.Cells
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Sheets("sheet1").Select
End Sub

 

[Don P]

Don,

I like your solution but could not get it to work on my system (also
xl2002). I made a slight adjustment and it worked fine.


Sub MakeValues()
Sheets.Select
Cells.Select
With Selection
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Sheets("sheet1").Select
End Sub

Don Pistulka

 

[Don Guillett]

Don,
Funny. I went back AGAIN and re-tested by starting a new wb with 5 tabs.
selected all sheets and put the same formula on each.
Executed the macro, as written.
Worked FINE..... to change all formulas on all sheets to values.
I am on an XP home system but that shouldn't make any difference.

 

[Tom Ogilvy]

Grouped sheet procedures in Excel VBA are very dependent on what is selected
on the worksheet. There is a good chance you (Don Guilett) had the
approapriate area already selected - thus your code would work. Don P,
overtly makes that selection, so his ensures it the appropriate area is
selected.

 

[Norman Harker]

Hi Henry!

Re:
I would like to save the workbook each month, converting all formulas
to values

Lateral thinking! You probably backup once a month too. Why not use
Ron de Bruin's SendMail Addin. With that you can use the Send Workbook
(Special) option that allows you to send a copy of the entire workbook
with just the current values. You could send that to yourself (your
never lonely writing to yourself!) with a new name or send it to a
backing up computer.

It's worth getting SendMail anyways. Free with User Guide from:

http://www.rondebruin.nl/Add-in

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Monday 21st July: Belgium (National Day),
Bolivia (Martyrs' Day), Guadeloupe (Victor Schoelcher Day), Guam
(Liberation Day), Japan (Marine Day), Malta (St. George Festa), Peru
(Feria Local Fronteriza Peruano Ecuatoriana), St. Martin (Schoelcher
Day).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
news:[email protected]...

 

[Ron de Bruin]

Norman posted the wrong link
Here is the good one
http://www.rondebruin.nl/sendmail.htm#Add-in