VBA to make Sheet Name = Cell Value

[JDSOuth49]

Good afternoon,

I am tiring to rename a sheet tab based on a specific cell.

I have attempted to use this code:

Sub rename_Sheet()
Dim OldName As String
OldName = ActiveSheet.Name
On Error Resume Next
ActiveSheet.Name = Range("A1").Value
On Error GoTo 0
If OldName = ActiveSheet.Name Then
MsgBox "Worksheet not renamed, Illegal name or no data available"
End If
End Sub

but it does not work for me.

I am pretty sure I am doing something incorrectly.

I am wanting to rename my sheet tab based on Cell "DQ7" with is a date.

Any help would be greatly appreciated.

 

[Joe4]

Try this line for the naming part:

ActiveSheet.Name = Format(Range("DQ7"),"dd-mmm")

 

[NoSparks]

try changing

ActiveSheet.Name = Range("A1").Value
to

ActiveSheet.Name = Range("DQ7").Text

 

[JDSOuth49]

I am shill having trouble. getting this message.

I believe that i failed to mention that there are 7 associated sheets as follows:
sheet 1 to sheet 7

 

[Joe4]

It look like you already have a sheet named "25-Feb", from your image above.

 

[JDSOuth49]

That is true, but I do not know how it got there a it does not go away.

I was thinking does this code need to be in each sheet or in the workbook?

 

[Joe4]

Why would it go away?

The point is this: you CANNOT have multiple sheets in the same workbook with the same name.
So if you already have a sheet named "25-Feb", you cannot name another sheet in that same workbook "25-Feb".

 

[Alex Blakenburg]

Incorporating Joe's suggestion. Try this but be warned if the DQ7 sheet already exists it "will delete it"

Sub rename_Sheet()
    Dim OldName As String
    Dim NewName As String
    
    OldName = ActiveSheet.Name
    NewName = Format(Range("DQ7"), "dd-mmm")
    
    If OldName = NewName Then
        MsgBox "Worksheet not renamed, Illegal name or no data available"
        Exit Sub
    End If
    
    On Error Resume Next
        Application.DisplayAlerts = False
            Worksheets(NewName).Delete
        Application.DisplayAlerts = True
        ActiveSheet.Name = NewName
    On Error GoTo 0
    
End Sub

 

[JDSOuth49]

Why would it go away?

The point is this: you CANNOT have multiple sheets in the same workbook with the same name.
So if you already have a sheet named "25-Feb", you cannot name another sheet in that same workbook "25-Feb".

Joe4

Sorry, I an still verry new to all of this.

I was the "active Sheet" that was messing me up. I did not realize what sheet i was in when I ran the code.

I did get the original one to work (As long as I was in the correct sheet. LOL)

I am now see that I need to specify the sheet Numbers in the code: I have tried this:
Sub rename_Sheet1()
Dim OldName As String
Dim NewName As String

OldName = Worksheets(“Sheet1”).Name

NewName = Format(Range("F17"), "dd-mmm")

On Error GoTo 0
If OldName = ActiveSheet.Name Then
MsgBox "Worksheet not renamed, Illegal name or no data available"
End If
End Sub
----BUT I keep getting an error message

From above:
In “Sheet1” I am wanting the contents from "F17" to be returned as the "NewName" for the worksheet.

Also, How is this updated? I suspect that every time cell "F17" is changed/update the Worksheet Name would automatically be up dated as well, Is this correct?

With that being said there are 7 sheets in total: (I am trying to get all sheets to have the worksheet names to auto populate for the current week that i am in (SUN ---> SAT) (2-25 --->3-1)

“Sheet1” = Cell "F17"
“Sheet2” = Cell "G17"
“Sheet3” = Cell "H17"
“Sheet4” = Cell "I17"
“Sheet5” = Cell "J17"
“Sheet6” = Cell "K17"
“Sheet7” = Cell "L17"

Thank you in advance.

 

[Joe4]

Replace all the slanted double-quotes with straight ones.
VBA does NOT like slanted double-quotes!