is there a way to change a formula to a value

[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

 

[Gary''s Student]

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.

 

[roadworthy]

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

You do a copy, then Paste Special. The keyboard shortcuts are
Ctrl - C
Alt- E, S V, Enter, Esc

 

[Gord Dibben]

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

 

[des-sa]

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.

 

[des-sa]

pushing even further - and maybe opens the template again for the new quote

 

[Gord Dibben]

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