How do I "PUSH" a cell value from one Worksheet to Another?

[Allen]

An easy example spells out my question the best:
On excell 2003 I have one worksheet (WS1) with a values:

A
1 2
2 2
3 +a1+a2

The formula in WS1!A3 results in a cell value of course 4. I desire to
"PUSH" the result in cell WS1!A3 to a new worksheet (WS2) to cell WS2!A1. In
most cases I would go to WS2 cell A1 & enter =WS1!A3.

This does not work in this case becase:
I need to "Push" the value to WS2 and still be able to type over the cell
WS2!A1 and the next time the worksheet is opened/updated the new value will
be pushed from WS1!A3 to WS2!A1.

i.e. How can I push the value and still be able to type over the cell to
adjust the value and STILL keep the unlying formula for the next time there
is a change or I open the workseet again.

I am currently opening the worksheet have all the needed transfers and then
typing over the formulas if some data needs adjusted - resaving the sheet
with another name so my underlying formulas are intact, but when I give this
sheet to others they will not be so carefull.

Please Help?

 

[Don Guillett]

Use a worksheet open event in the ThisWorkbook module

Private Sub Workbook_Open()
Sheets("sheet1").Value = _
Sheets("sheet2").Value
End Sub

 

[Allen]

Don thank you for your fash help!

I am not up to speed on MVB. I have been reading MVB online all afternoon.
The commands for copying [Book1]Sheet1!A3 to [Book1]Sheet2!A1 upon opening of
[Book1] would be: (I tried my best but it did not work).

Private Sub Workbook_Open()

 

[Don Guillett]

Assuming book1 is your active workbook and you DID put in the ThisWorkbook
module then you had it backwards to copy the formula as a value

Private Sub Workbook_Open()
Sheets("sheet2").Range("A1").value = _
Sheets("sheet1").Range("A3").value
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Don thank you for your fash help!

I am not up to speed on MVB. I have been reading MVB online all afternoon.
The commands for copying [Book1]Sheet1!A3 to [Book1]Sheet2!A1 upon opening
of
[Book1] would be: (I tried my best but it did not work).

Private Sub Workbook_Open()

Worksheets("Book1").Sheets("sheet1").Range("A3").value = _
Worksheets("Book1").Sheets("sheet2").Range("A1")
End Sub

Use a worksheet open event in the ThisWorkbook module

Private Sub Workbook_Open()
Sheets("sheet1").Value = _
Sheets("sheet2").Value
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Allen]

Thank You Don,

That fixed it. I am going to byu a MVB book this weekend to get up to speed.
I now see you can do alot in MVB and woth some learning effort.

Thank You Again for your help.

Assuming book1 is your active workbook and you DID put in the ThisWorkbook
module then you had it backwards to copy the formula as a value

Private Sub Workbook_Open()
Sheets("sheet2").Range("A1").value = _
Sheets("sheet1").Range("A3").value
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Don thank you for your fash help!

I am not up to speed on MVB. I have been reading MVB online all afternoon.
The commands for copying [Book1]Sheet1!A3 to [Book1]Sheet2!A1 upon opening
of
[Book1] would be: (I tried my best but it did not work).

Private Sub Workbook_Open()

Worksheets("Book1").Sheets("sheet1").Range("A3").value = _
Worksheets("Book1").Sheets("sheet2").Range("A1")
End Sub

Use a worksheet open event in the ThisWorkbook module

Private Sub Workbook_Open()
Sheets("sheet1").Value = _
Sheets("sheet2").Value
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
An easy example spells out my question the best:
On excell 2003 I have one worksheet (WS1) with a values:

A
1 2
2 2
3 +a1+a2

The formula in WS1!A3 results in a cell value of course 4. I desire to
"PUSH" the result in cell WS1!A3 to a new worksheet (WS2) to cell
WS2!A1.
In
most cases I would go to WS2 cell A1 & enter =WS1!A3.

This does not work in this case becase:
I need to "Push" the value to WS2 and still be able to type over the
cell
WS2!A1 and the next time the worksheet is opened/updated the new value
will
be pushed from WS1!A3 to WS2!A1.

i.e. How can I push the value and still be able to type over the cell
to
adjust the value and STILL keep the unlying formula for the next time
there
is a change or I open the workseet again.

I am currently opening the worksheet have all the needed transfers and
then
typing over the formulas if some data needs adjusted - resaving the
sheet
with another name so my underlying formulas are intact, but when I give
this
sheet to others they will not be so carefull.

Please Help?