Macro change on rename

[Guest]

I am using a macro to place a timestamp on workbook open and it saves it at
this time but if i rename the work book, then try to run the macro i receive
a file not found.

Private Sub Workbook_Open()
Worksheets("Sheet4").Range("I1").Value = Format(Now, "dd mmm yyyy
hh:mm:ss")
End Sub
Sub DateTime()

Worksheets("Sheet4").Range("I1").Value = _
Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub
The above code is what i am using and it works but the renaming of the file
could be an issue.Is there a work around on this?

 

[NickHK]

There's nothing in the code to indicate the filename at all.
Or do you mean renaming the worksheet ?

NickHK
P.S. Is this a .CSV file or a true Excel .xls file ?

 

[Guest]

This is an excel file and i have code to work on workbook open and also for
the time and that is placed in a module of the workbook. I created a custom
button to activate the macro to current time then save and it works fine.The
problem is if i want to rename the workbook then try to run the macro, it
does not recognize the file.

 

[NickHK]

So, it has nothing to do with the code you posted, but to with the location
from which you are calling this code ?
Where is the calling button/code located ?

If in Personal.xls, why not change the routine to insert the formatted
date/time in the current cell. Then it will work for any WB/WS you have open
and the filename will not matter :
Sub InsertDateTime()
ActiveCell.Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End Sub

You should add error handling in case the above is invalid.

Or depending how you are creating the button, you can specify the workbook
with ThisWorkbook.Name .

NickHK

 

[Stopher]

I am as confused as Nick on this, the code you mention above simply
changes a timedate stamp on Sheet 4.

You are try to save this workbook as another name, and then when you
open the copied workbook the macro won't run?

 

[Roger Govier]

Hi

Answered in your original thread in excel.misc