Forum Discussion

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...

Macros and VBA

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