Change Sheet Name to Cell Value (Date)

[NicoleLF]

Im trying to get the name of my sheet to reflect the same date i have in cell E9 on my sheet. (this is the location on all my sheets where the date is reflected) I was trying to use VBA code on the sheet but i cannot get it right. Could someone please help me?

 

[Candyman8019]

Try this. NOTE, you cannot use invalid characters such as '/' for tab names...so you'll need to ensure your date in E9 is formatted correctly, or format it in VBA.

Private Sub ChTabName()
    ActiveSheet.Name = Range("E9").Value
End Sub

 

[Candyman8019]

Example changing format...

Private Sub ChTabName()
    ActiveSheet.Name = Format(Range("E9").Value, "mm-dd-yyyy")
End Sub

 

[Herakles]

Im trying to get the name of my sheet to reflect the same date i have in cell E9 on my sheet. (this is the location on all my sheets where the date is reflected) I was trying to use VBA code on the sheet but i cannot get it right. Could someone please help me?

Welcome to MrExcel

What format do you want the date to have bearing mind the sheet naming rules?

This code will change the sheet name but needs to be altered depending on how you want the data formatted.

Try it with just random text in cell E9.

Put this cde in the sheet code page.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    With ActiveSheet
        If Target = .Range("E9") Then
            On Error Resume Next
            .Name = .Range("E9").Value
            On Error GoTo 0
        End If
    End With
    
End Sub

 

[NicoleLF]

Thank you both but neither of those options worked out for my sheet

Date format I have is ddmmmyy

Cell e9 is the date

When I type a date into the cell I would like the sheet name to change to that same date. Currently I manually type them :/

 

[Candyman8019]

To clarify, you would enter '17oct2023' into E9? If so, give this a try:

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("E9")) Is Nothing Then
Application.EnableEvents = False
    On Error Resume Next
    ActiveSheet.Name = Format(Range("E9").Value, "ddmmmyy")
Application.EnableEvents = True
End If
End Sub