Converting a formula to its value

  • Thread starter Thread starter macik81
  • Start date Start date
M

macik81

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
(e-mail address removed)
 
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
 
Back
Top