Rename sheet based on a cell value

[JERRY89]

Dear All,

I do have a command to rename sheet based on a cell value(Date) but the problem is whenever I copy another sheet it appear problem when i try to change the Date in Cell A1, reason is Cell Name is already taken. So is there a way to solve this problem ?

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

For i = 1 To Sheets.Count
    If Worksheets(i).Range("a1").Value <> "" Then
    
        Sheets(i).Name = Format(Worksheets(i).Range("A1").Value, "dd-mm-yyyy")

    End If
    
Next i
End Sub

[HansV]

It's not a good idea to name ALL sheets with the current date, and also it's not a good idea to rename sheets each time you select a different cell or range.
What do you want to accomplish?

[JERRY89]

Hi Hans,

Currently i having an excel workbook where i need to manually rename sheet once based on the Date in Cell A1, so i need the Macro to auto change the date once Cell A1 is change. The problem is when i Move a copy of the same sheet this macro detected Duplicate sheet name when cell A1 is Click. Is there a way to solve this??

[HansV]

1) You should use the Workheet_Change event instead of the Worksheet_SelectionChange event.
2) You should only rename the sheet whose code module contains the code.
3) If you want to do this for all worksheets, don't use code in each worksheet module, but instead, create a Workbook_SheetChange event procedure in the ThisWorkbook module:

Code: Select all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim i As Long
    If Not Intersect(Sh.Range("A1"), Target) Is Nothing Then
        If IsDate(Sh.Range("A1").Value) Then
            On Error Resume Next
            Sh.Name = Format(Sh.Range("A1").Value, "dd-mm-yyyy")
            If Err Then
                i = 1
                Do
                   On Error Resume Next
                   i = i + 1
                   Sh.Name = Format(Sh.Range("A1").Value, "dd-mm-yyyy") & "_v" & i
                Loop Until Err = 0
            End If
        End If
    End If
End Sub

[JERRY89]

Hi Hans,

Sorry for late reply...thanks alot ya...it help me alot...