Rename Cell to Various Different Sheet Titles

"Having to do it manually at the scale required would be quite difficult."
That seems unlikely.  The "scale" is one new worksheet at a time.

But perhaps you meant that manually creating the initial list of existing worksheets would be tedious.  Yes, it could be (for example, if you inherited a workbook with hundreds of worksheets).  This post suggests how that could be done readily.

A better-behaved macro would not modify an entire column on the active worksheet (however important that may be!) without user confirmation, leaving no way to undo the data change.  So consider this code instead:

Sub LoadListOfWorksheets()
'   This macro creates a list of the worksheets in the active workbook
'       into column A (see the constant below) of the active worksheet,
'       subject to confirmation by the user.

    Const strCOLUMN_ID = "A"    'for output
    Const in4FIRST_ROW_TO_USE As Long = 2   'for output
    
    Dim objWkbook       As Workbook
    Dim objListWksht    As Worksheet
    Dim strFullColumn   As String   'identifies the range
    Dim in4CellsWithContent As Long '…within the range
    '  --   Related to the user confirmation:
    Dim dstrMessage     As String
    Dim in4Icon         As Long
    Dim in4UserResponse As VbMsgBoxResult
    '  --   Indexes, etc.:
    Dim in4Wksht        As Long '…within the Sheets collection
    Dim in4OutputRow    As Long
    
    '----   Capture information and do preparations.
    Set objWkbook = ActiveWorkbook
    Set objListWksht = ActiveSheet
    '
    strFullColumn = strCOLUMN_ID & ":" & strCOLUMN_ID
    
    '----   Get user confirmation.
    in4CellsWithContent = WorksheetFunction.CountA( _
            objListWksht.Range(strFullColumn))
    dstrMessage = "Is it OK to put the worksheet names into column " _
            & strCOLUMN_ID _
            & vbCrLf & " of worksheet " & objListWksht.Name _
            & vbCrLf & " in workbook " & objWkbook.Name & "?"
    If in4CellsWithContent > 0 Then
        dstrMessage = dstrMessage & vbCrLf & vbCrLf _
                & "WARNING: Column " & strCOLUMN_ID & " has " _
                & Format$(in4CellsWithContent, "#,###,###,##0") _
                & " cell" & IIf(in4CellsWithContent = 1, "", "s") _
                & " that contain" & IIf(in4CellsWithContent = 1, "s", "") _
                & " some content.  That content will be erased !!"
        in4Icon = vbExclamation
    Else
        in4Icon = vbQuestion
    End If
    in4UserResponse = MsgBox(dstrMessage, in4Icon Or vbYesNo _
            Or vbDefaultButton2, "Load List of Worksheets")
    If in4UserResponse = vbNo Then Exit Sub
    
    '----   To improve performance of the remaining code, turn off
    '       screen updating.
    Application.ScreenUpdating = False
    
    '----   Clear any existing content.
    With objListWksht.Range(strFullColumn)
        .ClearContents
        .NumberFormat = "@" 'to prevent Excel from converting certain _
                worksheet names to a date or number or Boolean
    End With
    
    '----   Create an unsorted list of all worksheet names.
    in4OutputRow = in4FIRST_ROW_TO_USE
    For in4Wksht = 1 To objWkbook.Sheets.Count
        objListWksht.Range(strCOLUMN_ID & in4OutputRow).Value = _
                objWkbook.Sheets(in4Wksht).Name
        '
        in4OutputRow = in4OutputRow + 1
    Next in4Wksht
    
    '----   Restore setting(s).
    Application.ScreenUpdating = True
End Sub

I will leave it as an exercise for you to write worksheet names into one row, rather than into one column.

Note that this macro would not even have to be stored in the relevant workbook.  You could place it into your Personal.xlsb workbook.

As for automatically updating a list of workbooks (whether in a range of cells or an Excel table), that's complicated.  See the attached workbook for more information and example VBA code.

Edit: fixing another example of the forum dropping the file that I attached

Edit: trying a third time

Edit: and a fourth time