saving cell value for use later

[MitzDriver]

I have a worksheet that has a simple formula. In a1 is xxxnumber, a2 is
xxxnumber in a3 is =a1+a2. When i close the workbook I need to save the value
in a3 so that when i reopen the workbook, a2 is blank and a1 contains the
value from a3 in the previous workbook. Hope that made since.
Thanks for any suggestions.

 

[Luke M]

Will need to use visual basic. Open the VBE (Alt+F11) and in the project
explorer box (usually top-left) double click on the ThisWorkbook module.
Paste the following in, and then modify accordingly:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
'Define the sheet your values are on
With Sheets("Sheet1")
'Define Ranges as appropriate
..Range("A1").Value = .Range("A3").Value
..Range("A2").ClearContents
End With
End Sub


Close out the VBE, and you should be good to go. Macro will activate when
you save.

 

[Mike H]

Hi,

Alt+F11 to open Vb editor. Double clicj 'ThisWorkbook' and paste the code
below in on the right.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Sh As Object
Set Sh = Sheets("Sheet1")
With Sh
.Range("A1").Value = Range("A3").Value
.Range("A2").ClearContents
End With
End Sub

Mike

 

[MitzDriver]

Thanks Luke and Mike for the quck response. It all looks good but....the
orginal workbook is a template. I will be saving it as a seperate wookbook
with all the data in place.

When I open the template again I need that value from a3 to show up in a1 of
the new workbook.

i.e. open template "BingoTemp" insert values in a1 and a2, value of a3 now
is, a1+a2. Close and save workbook as "Bingo10-02-2009" for later review.
Open template "BingoTemp" again and the of a3 in "Bingo10-02-2009" is now in
a1 of "BingoTemp".

Thanks again for your input.