saving values to cells

[bouncebackability]

ok here goes...

what i was wondering is hard to explain so ill explain it in an smal
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
in cell B2 on the 'week2' sheet

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

but i then want to delete sheets 'week1' and 'week2' yet keep the valu
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 value
to the cells.

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

thanks in advance

 

[Bill Kuunders]

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

<cntr>C to copy
<alt>ESV 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.

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3