Automatically name worksheets based on cell values from master sheet

timlh42

Board Regular
Joined
Sep 27, 2017
Messages
76
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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?

Code:
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
 
Last edited:
Upvote 0
For some reason I keep getting a run time error 1004 and the

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

is highlighted
 
Upvote 0
Try this:
Code:
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
 
Upvote 0
Try this:
Code:
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.
 
Upvote 0
What is the error message?
Also how many sheets do you have?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top