Subsequent Sheets Renamed From Cells Within Sheet 1

[Jason1H]

Good Day All,

I am working on project estimating workbook. I have currently have 12 worksheets within this file. First sheet is labelled "Project Page", second sheet is labelled "Labor Break Down", subsequent sheets are default to sheet 3, sheet 4, etc..... Is there a way we can rename worksheets 3-10 based on cell data entered on the first worksheet that is labelled" Project Page". So if I enter "PKG-10" in cell A1, "PKG-20" in cell A2, "PKG-30" in cell A3 of the "Project Page. Then worksheet #3 gets relabelled to "PKG-10", worksheet #4 gets relabelled to "PKG-20", worksheet #5 get relabelled to "PKG-30" etc... The data entered into cells A1 thru A10 will vary from project to project. This relabelling will need to be automatic without the need to run a macro if possible. If macro is required, I can insert a button on the "Project Page".

Alternately is there an easier way to have the sheets rename based on cell data entered into A1-A10 of the "Project Page" with the Name Manager within the Formulas ribbon tab??

 

[Micron]

The For loop will only work properly if the names of the sheets are named 'Sheet3', 'Sheet4', ''Sheet5', etc.

Ja, which they are/were?

subsequent sheets are default to sheet 3, sheet 4, etc.....

Wouldn't matter if there were 20 sheets named like that either?
Maybe you didn't declare the string variable thus the compile error? Don't know if you use Option Explicit or not, but I do, and that compiled for me.

 

[johnnyL]

Corrected/Updated code from post #7 ...

This code can change the sheet names for all all sheet codenames 'Sheet3' through 'Sheet12'.
It doesn't matter what order the sheet tabs at the bottom of the sheet are in.
It allows numerous sheet name changes.

Changing A1 equates to the sheet name that you want the sheet with the codename 'sheet3' to be.
Changing A2 equates to the sheet name that you want the sheet with the codename 'sheet4' to be.
...
Changing A10 equates to the sheet name that you want the sheet with the codename 'sheet12' to be.

Private Sub Worksheet_Change(ByVal Target As Range)
'
    Dim x               As Long
    Dim SheetToChange   As Worksheet, WS    As Worksheet
'
    If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub  ' If cell that was changed on the sheet is not in the range of A1:A10 then Exit this subroutine
'
    Application.EnableEvents = False                                ' Turn EnableEvents off to prevent possible endless looping of code
'
    For x = 1 To 10                                                 ' Loop through range of A1:A10
        If Not Intersect(Target, Range("A" & x)) Is Nothing Then    '   If the changed cell in range A1:A10 is found then ...
            On Error Resume Next                                    '       Turn Excel error handling off, if error found in line of code ... proceed to next line of code
'
            For Each WS In Worksheets                               '       Loop through all of the sheet names
                If WS.CodeName = "Sheet" & x + 2 Then               '           If the codename of the sheet matches the codename that we are looking for then ...
                    Set SheetToChange = WS                          '               Save the sheet name to SheetToChange
                    Exit For                                        '               Exit this For loop
                End If
            Next WS                                                 '       Loop back
'
            On Error GoTo 0                                         '       Return error handling back over to Excel
'
            If Not WS Is Nothing Then                               '       If no error, then the sheet does exist so ...
                SheetToChange.Name = Range("A" & x)                 '           Change the sheet name
                Exit For                                            '           Exit this For loop
            End If
        End If
    Next                                                            ' Loop back
'
    Application.EnableEvents = True                                 ' Turn EnableEvents back on
End Sub