CODEWITHSUNDEEP
excel
noob
noob

Reputation: 279

Dynamic Sheet name in Excel as DATE

i found a code on the internet and tried it out. The code works. but my concern is that i wish to rename the sheet as Date. or at least allow the code to read the format mm/dd/yyyy from the cell.

here is the sample of the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set Target = Range("A1")
    If Target = "" Then Exit Sub
    Application.ActiveSheet.Name = VBA.Left(Target, 31)
End Sub

if the value of A1 is 06/01/2016, i would like to have my sheet name as Jun01,2016 or at least have the name as Date.

Note.. i also tried other suggestion(s) from other sites but still not working

Upvotes: 1

Views: 4214

Answers (2)

user6432984
user6432984

Reputation:

We might need to tweak this if the date is stored as text. Let me know.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If IsDate(Range("A1")) Then

        ActiveSheet.Name = Format(Range("A1"), "MM-DD-YYYY hh-mm-ss")

    End If
End Sub

Upvotes: 2

user4039065
user4039065

Reputation:

I'm unsure why this has to be a Worksheet_SelectionChange event macro or why you are rewriting the Target parameter but perhaps this will work.

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
    If Target.Address <> "$A$1" or Not IsDate(Range("A1")) Then Exit Sub
    Me.Name = Format(Target.Value2, "mmmdd,yyyy")
End Sub

Select A1 (and just A1) with a date (possibly =today()) in A1 and the worksheet's name will be adjusted (e.g. Jul07,2016).

Upvotes: 1

Related Questions