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?

 

[Fluff]

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?

 

[timlh42]

I haven't created those sheets yet. I was just trying to get the code in first.

Should I go ahead and create them?

 

[My Aswer Is This]

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"

 

[timlh42]

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.

 

[MoshiM]

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 ?

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

 

[Fluff]

How about

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.

 

[timlh42]

I will try it in a moment and let you know.

 

[timlh42]

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?

 

[Fluff]

Yup, use this

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

 

[timlh42]

I replaced Sheets.Add.Name = .Cells(i, 1) with

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

and It gave an error