Name Sheet Tab to Reflect Edit Date

[Gerard Sanchez]

'Hi,

'I got this simple code, I was wondering if anybody here can help me put
the 'value of "_timestamp" (see below) as name of my worksheet.


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now

End Sub

 

[OssieMac]

Hi Gerard,

Set the format "dd mmm yyyy hh_mm_ss" in the code to whatever format you
want. However, you cannot use some characters like a colon ) in the sheet
name so can't use them in the date/time format.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()

'Remove the leading = sign from the value and save to date variable
dateTemp = Val(Mid(ActiveWorkbook.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "dd mmm yyyy hh_mm_ss")

End Sub

 

[OssieMac]

Hi again Gerard,

I had an error in the previous post. It works OK but because you had already
set the scope of the name to ActiveSheet in the previous line of code, I
should not have used ActiveWorkbook in the line that assigns the date to
dateTemp. Use the following corrected code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()

'Remove the leading = sign from value and save as date variable
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "dd mmm yyyy hh_mm_ss")

End Sub

 

[Gerard Sanchez]

It worked!
Thank you OssieMac for helping and taking the time

Cheers!

--gerard