■ はじめに

 * Excelでドキュメントを作成した際に、シートが増えると、
   目次を作成して、その目次にリンクを張っておけば便利だが
   その目次を自動生成できるようにする

 ■ サンプル

Sub CreatContents_Click()
On Error GoTo ErrorHandler
    Const ContentsSheetName As String = "目次"
    Dim contentsSheet As Worksheet
    Set contentsSheet = Worksheets(ContentsSheetName)
    
    Dim number As Integer
    number = 1

    ' 出力する行数（初めは表の項目を書きたいので初期値は2)
    Dim line As Integer
    line = 2
    
    Dim targetWorksheet As Worksheet
    For Each targetWorksheet In Worksheets
        If ContentsSheetName = targetWorksheet.Name Then
            ' シート名「目次」は対象外
            GoTo Continue
        End If
        
        ' 項番
        contentsSheet.Cells(line, 1).Value = number
        number = number + 1
        
        ' シート名
        targetWorksheet.Hyperlinks.Add Anchor:=contentsSheet.Cells(line, 2), _
                                  Address:="", _
                                  SubAddress:="'" & targetWorksheet.Name & "'!A1", _
                                  TextToDisplay:=targetWorksheet.Name
        
        line = line + 1
Continue:
    Next
    
    MsgBox "Done"
    GoTo Finished
ErrorHandler:
    '-- 例外処理
    MsgBox Err.number & " : " & Err.Description, vbCritical & vbOKOnly, "Error"
Finished:
End Sub

 出力結果

* シートが「目次」「Sheet1」「Sheet2」の場合 
項番  シート名  << ここは初めに書いておく
1   Sheet1  << ここがリンク
2   Sheet2  << ここがリンク

■ 補足

 補足1 : 括弧が入ったシート名の対処

 * シート名に括弧が入ると、リンクが動かなくなるので、
　 回避策として、SubAddressの値をシングルクォート(')を囲っている

 補足2 : 使用した技術事項

 * 使用した以下の技術事項

  [1] シートでループする
  [2] Continue文を実装
  [3] Try-Catch\

 関連記事

Excel マクロ ～ 入門編 ～
https://dk521123.hatenablog.com/entry/2015/07/15/104500