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.

--
Gary''s Student - gsnu200786


"des-sa" wrote:

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

On May 14, 7:54 am, des-sa wrote:
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

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

On Wed, 14 May 2008 07:54:02 -0700, des-sa
wrote:

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

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.

"Gord Dibben" wrote:

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

On Wed, 14 May 2008 07:54:02 -0700, des-sa
wrote:

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

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

"Gord Dibben" wrote:

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

On Wed, 14 May 2008 07:54:02 -0700, des-sa
wrote:

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 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

On Wed, 14 May 2008 09:20:01 -0700, des-sa
wrote:

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

"Gord Dibben" wrote:

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

On Wed, 14 May 2008 07:54:02 -0700, des-sa
wrote:

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