Excel - sheet names equal to cell (but the cells giving name to the sheets are all in the same sheet)

Question

I would like to know if it is possible to have the sheet names equal to a specific cell of just one sheet.

For example:

I have a sheet called names

A1 = Name1

A2 = Name2

A3 = Name3

A4 = Name4

A5 = Name5

So the sheet names should be based on these five values

Sheet2 = Name1

Sheet3 = Name2

Sheet4 = Name3

Sheet5 = Name4

Sheet6 = Name5

Is this possible with VBA?

Thank you in advance for any help.

Answer 1

You could make it event triggered.

1) Place the below code in the worksheet object for the "Names" tab.

2) Put all the sheet code names in the "Names" sheet in the first column (no headers).

3) Put the tab names of your choice for the respective sheets in the second column.

The sheet names should change automatically when you change the values in the second column.

CODE

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sheetName As String
    Dim sheetCodeName As String

    If Target.Column = "2" Then
        sheetName = Target.value
        sheetCodeName = Target.Offset(0, -1).value

        For Each Sheet In ActiveWorkbook.Worksheets
            If Sheet.CodeName = sheetCodeName Then

                On Error GoTo INVALIDCOLUMNNAME:
                Sheet.name = sheetName
                On Error GoTo 0

            End If

        Next Sheet

    End If

Exit Sub

INVALIDCOLUMNNAME:
msgbox "Invalid Column Name"

End Sub