Automate the Creation of an Index in Excel using VBA: Generate Hyperlinks within the Workbook for Easy Navigation

Sub CreateHyperlinkIfValuesMatch()

    Dim indexSheet As Worksheet
    Dim statisticsSheet As Worksheet
    Dim indexCell As Range
    Dim statisticsCell As Range
    Dim linkAddress As String
    
    Set indexSheet = ThisWorkbook.Sheets("Sheet A") ' change the sheet name to the name of your "Index" sheet
    Set statisticsSheet = ThisWorkbook.Sheets("Sheet B") ' change the sheet name to the name of your "Statistics" sheet
    
    For Each indexCell In indexSheet.Range("A2:A97")
        If Not IsEmpty(indexCell) Then
            For Each statisticsCell In statisticsSheet.Range("A2:A97")
                If indexCell.Value = statisticsCell.Value Then
                    linkAddress = "'" & statisticsSheet.Name & "'!" & "A" & statisticsCell.Row
                    indexSheet.Hyperlinks.Add Anchor:=indexCell, Address:="", SubAddress:=linkAddress, TextToDisplay:=statisticsCell.Value
                    
                    Exit For ' no need to check the rest of the cells in column A if a match has been found "Link to " & statisticsSheet.Name & "!" & "A" & statisticsCell.Row

                End If
            Next statisticsCell
        End If
    Next indexCell
End Sub