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?
 
In your op you said you have values in A3 to A30, that's 28 values, but if you only have 7 sheets what should happen to the other 21 values?
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
I haven't created those sheets yet. I was just trying to get the code in first.

Should I go ahead and create them?
 
Upvote 0
In your original post you did not say you had not created the sheets.

I need to know what is the sheet name of the sheet with the names in range("A3:A30")

So you want the script to create a new sheet for every one of these values.
So the script will create 28 new sheets.

Is that what you want.

We always need specific details like this.

And the new sheets we create will be empty sheets correct? Or are you wanting to make 28 new sheets just like your Template sheet which is named "Template"
 
Last edited:
Upvote 0
I'm sorry that I wasn't very clear.

My sheet name with the rang(A3:A30) is Grading

I would be fine with the script creating new empty sheets or just renaming the existing sheets. Either way would work great.
 
Upvote 0
I haven't created those sheets yet. I was just trying to get the code in first.

Should I go ahead and create them?

What pops up in the message box when you use this ?

Code:
Sub Re_Name_Worksheet()

Dim WS_Names As Variant, Z As Long, Err_String As String

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

On Error Resume Next

With ThisWorkbook

    For Z = 2 To .Worksheets.count 'start at 2nd worksheet and then rename
        
        With .Worksheets(Z)
            
            .Name = WS_Names(Z - 1, 1)
       
            If Err.Number <> 0 Then
       
                Err_String = Err_String & "An error occured while renaming Worksheet [ " & .Name & " ] to " & _
                                          WS_Names(Z - 1, 1) & vbNewLine
                Err.Clear
                
            End If
        
        End With
        
        If Z = UBound(WS_Names, 1) - 1 Then Exit For 'exit this loop if all 28 sheets have been renamed or if there are no more names/worksheets available
        
    Next
    
End With

If Err_String <> vbNullString Then MsgBox Err_String

End Sub
 
Last edited:
Upvote 0
How about
Code:
Sub timlh42()
    Dim i As Long
    
    With Sheets("Sheet1")
        For i = 3 To 30
            If .Cells(i, 1) <> 22 Then
                If Not Evaluate("isref('" & .Cells(i, 1) & "'!A1)") Then
                    If Evaluate("isref('Sheet" & i - 2 & "'!A1)") Then
                        Sheets("Sheet" & i - 2).Name = .Cells(i, 1)
                    Else
                        Sheets.Add.Name = .Cells(i, 1)
                    End If
                End If
            End If
        Next i
    End With
End Sub
This will add the sheets if needed.
 
Upvote 0
Creating new sheets works really great. Is there any way to add the new sheets to the end instead of being before my "Grading" worksheet?
 
Upvote 0
Yup, use this
Code:
Sheets.Add(, Sheets(Sheets.Count)).Name = Cells(i, 1)
 
Upvote 0
I replaced Sheets.Add.Name = .Cells(i, 1) with

Sheets.Add(, Sheets(Sheets.Count)).Name = Cells(i, 1)

and It gave an error
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
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