Forum Discussion

MichelleJ2480's avatar
MichelleJ2480
Copper Contributor
Sep 10, 2024

VBA - Rename sheets with cell values - Error 1004

I'm trying to rename several sheets based on cell values but can't figure out how to check for duplicate values and add a number to those names.

 

This is the code I'm starting with, which gets the job done so long as no other worksheets have the same values in both F4 and E4. 

 

Sub Rename_Sheet()

ActiveSheet.name = Range("F4") & " to " & Range("E4")

End Sub

 

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 10, 2024

    MichelleJ2480 

    Here you go:

    Sub Rename_Sheet()
    Dim NewName As String
    Dim WS As Worksheet
    ' Get the new name
    NewName = Range("F4") & " to " & Range("E4")
    ' If this is the name of the active sheet, we're done
    If ActiveSheet.Name = NewName Then Exit Sub
    On Error Resume Next
    ' Test whether there is a sheet with the new name
    Set WS = Worksheets(NewName)
    If WS Is Nothing Then
        ' Rename the active sheet
        ActiveSheet.Name = NewName
    Else
        ' Inform the user
        MsgBox "There is already a worksheet named '" & NewName & "'!", vbExclamation
    End If
End Sub

Resources