Call the VBAProject sheet names in a macro

Question

Background:

I am trying to add hyperlinks to a list of cells direct to their corresponding sheet names, where the cell values were captured in a dictionary. Those are not like-for-like names as I have replaced spaces and special characters.

Issue:

I noticed my sheets in the VBAProject were Sheet2 as cells(2,1), so attempted a quick loop to input the hyperlinks. I sadly found out that for i = 2 to 26 // sheets(i).name did not align with my expectations (the cells(2,1) hyperlink displayed sheet25).

Inquiry:

How do I call the VBAProject sheet name within my macro so I can have an exact match up?

Code:

    With Sheets("Overview")
        Dim i As Long:  For i = 2 To 26
            Dim ws As String:  ws = Sheets(i).Name
            .Hyperlinks.Add Anchor:=.Cells(i, 1), Address:="", SubAddress:=ws & "!A1", TextToDisplay:=.Cells(i, 1).Value
        Next i
    End With

Answer 1

Create 'Code-Named' Hyperlinks

Sub CreateCodeNamedHyperlinks()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Sheets("Overview")
    Dim srg As Range:
    Set srg = sws.Range("A2", sws.Cells(sws.Rows.Count, "A").End(xlUp))
    srg.Hyperlinks.Delete  
        
    Dim scell As Range, dws As Worksheet, dwsCodeName As String
        
    For Each scell In srg.Cells
        
        dwsCodeName = CStr(scell.Value)
        
        On Error Resume Next
            Set dws = wb.Sheets(wb.VBProject.VBComponents(dwsCodeName) _
                .Properties("Name").Value)
        On Error GoTo 0
            
        If Not dws Is Nothing Then
            sws.Hyperlinks.Add Anchor:=scell, Address:="", _
                SubAddress:="'" & dws.Name & "'!A1", _
                TextToDisplay:=dwsCodeName
            Set dws = Nothing ' reset
        End If
        
    Next scell

End Sub