Automatically name worksheets based on cell values from master sheet

[timlh42]

I would like to have names listed in sheet1 in column A, beginning at A3 and ending on A30.

So if there is a name in A3 ( George) and (Sam) in A4, (John) in A5 and so on, I would like it to change the sheet names from Sheet2 to George, sheet3 to Sam, sheet4 to John, etc

Is this possible?

 

[MoshiM]

I would like to have names listed in sheet1 in column A, beginning at A3 and ending on A30.

So if there is a name in A3 ( George) and (Sam) in A4, (John) in A5 and so on, I would like it to change the sheet names from Sheet2 to George, sheet3 to Sam, sheet4 to John, etc

Is this possible?

Sub Re_Name_Worksheet()

Dim WS_Names As Variant, Z As Long

WS_Names = ActiveSheet.Range("A3:A30").Value

With ThisWorkbook

    For Z = 2 To .Worksheets.count 'start at 2nd worksheet and then rename
    
       .Worksheets(Z).Name = WS_Names(Z - 1, 1)
    
    Next

End With

End Sub

 

[timlh42]

Excellent!!!!!

Thank You!!!

 

[timlh42]

For some reason I keep getting a run time error 1004 and the

.Worksheets(Z).Name = WS_Names(Z - 1, 1)

is highlighted

 

[My Aswer Is This]

Try this:

Sub Sheet_Names()
'Modified 10/14/2019 9:14:55 AM  EDT
On Error GoTo M
Application.ScreenUpdating = False
Dim i As Long
For i = 3 To 30
    Sheets(i - 1).Name = Sheets(1).Cells(i, 1).Value
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "There was a problem." & vbNewLine & "You may have a duplicate sheet name or a improper sheet name"
End Sub

 

[timlh42]

Try this:

Sub Sheet_Names()
'Modified 10/14/2019 9:14:55 AM  EDT
On Error GoTo M
Application.ScreenUpdating = False
Dim i As Long
For i = 3 To 30
    Sheets(i - 1).Name = Sheets(1).Cells(i, 1).Value
Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "There was a problem." & vbNewLine & "You may have a duplicate sheet name or a improper sheet name"
End Sub

Sorry but I still keep getting the above message.

 

[Fluff]

What is the error message?
Also how many sheets do you have?

 

[timlh42]

What is the error message?
Also how many sheets do you have?

It is your embedded message. There was a problem. You may have a duplicate sheet name or a improper sheet name

My sheets are as follows:

Data, Comments, Grading, Sheet1, Sheet2, Sheet3, Sheet4, etc

 

[Fluff]

It wasn't my message.
When you ran the original code you got a 1004 error, what was the message that went with it?
Can you also please answer my other question?

 

[timlh42]

I have 7 sheets

The 1004 error code says

Appicatio- defined or object defined error