Time / Date stamping in a cell

[biccy g]

Hi All,

I am using the formula now() to put the date and time in a cell during a macro. Each day I run a new worksheet and save it, I went into yesterdays sheet and the now() dates times have all changed to literally now when I entered the spreadsheet. Is there anyway I can lock this time / date value, or does anybody know of a more sensible formula to use?

Many Thanks

 

[wongm003]

I've made template workbooks that I use and macros are run in ThisWorkbook Workbook_Open event to prevent the macros being run after the workbook is saved, I initially save the template workbook with "template" in the file name and when the file is saved take out the template... You can use any naming convention that you like.

Private Sub Workbook_Open()

    If ActiveWorkbook.Name Like "*template*" Then
        'run macros/perform code
        Range("A1").Value = Now()
    End If

End Sub

 

[sykes]

You say you're using the "formula" NOW().......... well if you put that FORMULA into a cell, every time that the sheet calculates it will update the contents of the cell.

If you use the "FUNCTION" NOW(), then the contents of the cell in question should remain static - AS LONG AS YOU DON'T KEEP RUNNING YOUR MACRO ON THE SAME SHEET.

Sub enter_date()
Me.[A1].Value = Now()
End Sub

This code, using the ME keyword will only work if you put it into the code module of the sheet in question, otherwise use the long reference to the worksheet.