ブック内の各シートに対する目次シート作成

1 For Eachの各シートからの戻りは,sheet(1)から順に終わりまで.順番は保障されているわけではない.戻る順番はコレクションオブジェクトにに依存するらしい。

VBA For Eachが順番を保証しない理由を自作のコレクションで説明 - t-hom’s diary

sheetsコレクションオブジェクトはsheets(1)からsheets(n)まで順番に戻るようなので、ForEachを使うと簡単に書ける.

 for next を使うと、戻り値の順番も確実にコントロールできる。

Excelで目次(シートへのリンク)を作るマクロ | EX-IT

2 

Option Explicit

Sub シート目次作成()
    '初期化
    Range("B:B").Clear
    
    Dim InputCell As Range: Set InputCell = ThisWorkbook.Sheets("目次").Range("A1")
    Dim Sh As Worksheet
    For Each Sh In Worksheets
        '各シートから目次に戻るリンク
        Hyperlinks.Add _
            anchor:=Sh.Range("A1"), _
            Address:="", _
            SubAddress:="目次!A1 ", _
            TextToDisplay:="目次へ"
'        Sh.Columns("A").AutoFit
        
        '目次シートに目次作成
        InputCell.Value = Sh.Name
        'シート名はシングルコーテーションで挟む
        Hyperlinks.Add _
            anchor:=InputCell, _
            Address:="", _
            SubAddress:="’" & Sh.Name & "’" & "!A1", _
            TextToDisplay:=Sh.Name
        
        Set InputCell = InputCell.Offset(1, 0)
    Next
End Sub