is there a way to change a formula to a value

  • Thread starter Thread starter des-sa
  • Start date Start date
D

des-sa

is there a way to change a cell's contents, upon saving the file, from a
formula to a value. or as soon as the file has opened.
thanks
 
Put this event macro in the workbook code area:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("Sheet3").Range("A1")
..Value = .Value
End With
End Sub

It replaces the formula in cell A1 in sheet Sheet3 with its value.
 
is there a way to change a cell's contents, upon saving the file, from a
formula to a value. or as soon as the file has opened.
thanks
Click to expand...

You do a copy, then Paste Special. The keyboard shortcuts are
Ctrl - C
Alt- E, S V, Enter, Esc
 
With Workbook event code, either BeforeSave or Workbook_Open.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
With Sheets("Sheet1").Range("A10")
..Value = .Value
End With
End Sub


Gord Dibben MS Excel MVP
 
thank you Gord - works 100%

can i now push my luck. i have 2 buttons, each with a macro attached to it.
one for saving the file with the date and document number as file name, the
other to print the document.

could it not be possible to have a macro or vba script attached to a single
button which, when pressed, print 2 copies of the document, saves the
document and closes it.
 
You would need to combine your two macros into one and add a couple of lines

Sheets("Sheet1").PrintOut Copies:=2
Workbooks.Add Template:="your path to the Template"

' your code to Save and close the first workbook


Gord
 
Back
Top