[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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Allen" wrote in message
...
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?

[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()
Worksheets("Book1").Sheets("sheet1").Range("A3").v alue = _
Worksheets("Book1").Sheets("sheet2").Range("A1")
End Sub


"Don Guillett" wrote:

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

"Allen" wrote in message
...
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]

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

"Allen" wrote in message
...
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").v alue = _
Worksheets("Book1").Sheets("sheet2").Range("A1")
End Sub


"Don Guillett" wrote:

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

"Allen" wrote in message
...
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?

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


"Don Guillett" wrote:

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

"Allen" wrote in message
...
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").v alue = _
Worksheets("Book1").Sheets("sheet2").Range("A1")
End Sub


"Don Guillett" wrote:

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

"Allen" wrote in message
...
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?