Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving a value in another cell | Excel Worksheet Functions | |||
Help with saving from a cell | Excel Programming | |||
Saving with a filename already in a cell | Excel Programming | |||
Saving a file with a name from a cell value. | Excel Programming | |||
Saving a file with a name from a cell value. | Excel Programming |