I am writing a program that updates a large excel workbook on a weekly
basis. There are a couple of worksheets that I copy in the current
weeks data and the remaining worksheets perform their calculations
based on the current data. Is there a way to convert the cells
containing formulas to their values, so that next week when I run the
program the previous weeks data will remain?

Thanks in advance.

Copy it and paste special...+Values over itself

Like this

Sub KillFormulas()
Dim myRng As Range
Set myRng = Range("A1:H18")
With myRng
.Copy
.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


wrote in message
ups.com...
I am writing a program that updates a large excel workbook on a weekly
basis. There are a couple of worksheets that I copy in the current
weeks data and the remaining worksheets perform their calculations
based on the current data. Is there a way to convert the cells
containing formulas to their values, so that next week when I run the
program the previous weeks data will remain?

Thanks in advance.

try this:
if your formula are in cell F56 to F9

Sub Macro1()
Range("F6:F9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("F6").Select
Application.CutCopyMode = False
End Sub


--
Herve_Rob
------------------------------------------------------------------------
Herve_Rob's Profile: http://www.excelforum.com/member.php...o&userid=35204
View this thread: http://www.excelforum.com/showthread...hreadid=549684

columms(1).value=columns(1).formula

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
I am writing a program that updates a large excel workbook on a weekly
basis. There are a couple of worksheets that I copy in the current
weeks data and the remaining worksheets perform their calculations
based on the current data. Is there a way to convert the cells
containing formulas to their values, so that next week when I run the
program the previous weeks data will remain?

Thanks in advance.