private sub workbook_close()

[d0wnt0wn]

Hi there I have a formula that runs on work book open to put in todays date if the xls file is named estimate.

If ActiveWorkbook.name = "estimate.xls" Then Range("c1").Formula = "=today()"

how can i set the same range to keep the date thats in it but lose the =today() formula when the workbook closes IF the workbook has been saved under a different name.

cheers

Ken

 

[eforti]

One way to do it would just be to copy and paste special prior to closing. You would just need to copy and paste the coding into your workbook_close sub.

Range("A1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[d0wnt0wn]

ok so i need a code that will do this:

on workbook close

if active sheet ="estimate.xls" then do nothing
if active sheet ="any other name" then
copy and paste the contents of c1 as values only

could someone please help me with this i know what i want just not how to write it

Ken

 

[d0wnt0wn]

ahhh its ok... i ut realized that it does not matter if it copy and pastes if the workbook is named estimate.xls because the next time it opens the today() code will run again

the copy paste code is all i need thanks

 

[lenze]

Change your code, dumping the formula

If ActiveWorkbook.name = "estimate.xls" Then Range("c1") = Date

lenze