saving cell value for use later
 

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.

saving cell value for use later
 

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.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"MitzDriver" wrote:

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.

saving cell value for use later
 

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" wrote:

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.

saving cell value for use later
 

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.

"Mike H" wrote:

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" wrote:

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.