saving values to cells
 

ok here goes...

what i was wondering is hard to explain so ill explain it in an small example...

i have 3 sheets in my workbook, 'week1', 'week2' and 'total'

i enter the number 10 in cell A1 on the 'week1' sheet and the number 3 in cell B2 on the 'week2' sheet

on the 'total' sheet in cell C3 i enter the formula =week1!A1+week2!B2 and it adds it obviously.

but i then want to delete sheets 'week1' and 'week2' yet keep the value 13 in cell C3 on the 'total' sheet WITHOUT having to type it in myself.

ignore the macro part i would like to know just how to lock the values to the cells.

is this even possible? im using excel 2000 but have access to 2003 if necessary. any response would be wonderful :)

thanks in advance.

saving values to cells
 

The cell Total!C3 needs to be changed from a formula to a value
select the cell

<cntrC to copy
<altESV to paste special "values"

You can put this into an auto macro
say before save

Say your total is in cell C3 on sheet3
enter the macro below into the workbook code.
To get there ,,,,,,,,right click onto the excel sign next to the "file" menu
on the top menu bar
select view code

select "workbook" in the top left dropdown and "before save" in the right
dropdown


You'll see.............
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
End Sub

Copy the following lines............and paste them between Private
Sub...........and End Sub

Sheet3.Select
Range("C3").Copy
Range("C3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

So the end result will be...............

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Sheet3.Select
Range("C3").Copy
Range("C3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

From now each time you save the workbook the contents of cell C3 will be
copied and pasted
as a value.


--
Greetings from New Zealand

"bouncebackability" wrote in
message ...

ok here goes...

what i was wondering is hard to explain so ill explain it in an small
example...

i have 3 sheets in my workbook, 'week1', 'week2' and 'total'

i enter the number 10 in cell A1 on the 'week1' sheet and the number 3
in cell B2 on the 'week2' sheet

on the 'total' sheet in cell C3 i enter the formula =week1!A1+week2!B2
and it adds it obviously.

but i then want to delete sheets 'week1' and 'week2' yet keep the value
13 in cell C3 on the 'total' sheet WITHOUT having to type it in myself.

ignore the macro part i would like to know just how to lock the values
to the cells.

is this even possible? im using excel 2000 but have access to 2003 if
necessary. any response would be wonderful :)

thanks in advance.




--
bouncebackability

saving values to cells
 

Copy PasteSpecial Values, will delete the formula and retain the result...

Vaya con Dios,
Chuck, CABGx3



"bouncebackability" wrote:


ok here goes...

what i was wondering is hard to explain so ill explain it in an small
example...

i have 3 sheets in my workbook, 'week1', 'week2' and 'total'

i enter the number 10 in cell A1 on the 'week1' sheet and the number 3
in cell B2 on the 'week2' sheet

on the 'total' sheet in cell C3 i enter the formula =week1!A1+week2!B2
and it adds it obviously.

but i then want to delete sheets 'week1' and 'week2' yet keep the value
13 in cell C3 on the 'total' sheet WITHOUT having to type it in myself.

ignore the macro part i would like to know just how to lock the values
to the cells.

is this even possible? im using excel 2000 but have access to 2003 if
necessary. any response would be wonderful :)

thanks in advance.




--
bouncebackability