【VBA】エクセルブック内の大量のシートの目次を自動作成する方法

どーも！marusukeです！

エクセルブック内の大量のシートの目次を自動作成する方法VBAコードを紹介します！

このVBAでできること（結論）
このVBAの処理の流れ
コピペで使えるVBAコード
カスタマイズする場合のポイント
よくあるエラー・注意点
まとめ

このVBAでできること（結論）

ブック内の全シート名を一覧化して「目次」シートに自動出力します
各シート名にクリックで飛べるリンクを付け、探す手間を置き換えます
目次シートが無ければ自動作成、あれば内容を作り直します
シート数が多いブックほど「探す」「戻る」の繰り返しを減らせます

このVBAの処理の流れ

「目次」シートがあるか確認（無ければ作成）
目次シートの内容を一旦クリアして、見出しを作成
ブック内の全シートを順番に走査（目次シート自身は除外）
シート名を目次に書き出し、各行にジャンプ用リンクを付ける

コピペで使えるVBAコード

Sub MakeSheetIndex()

    Dim wsIndex As Worksheet
    Dim ws As Worksheet
    Dim r As Long
    Dim indexName As String
    
    indexName = "目次"
    
    Application.ScreenUpdating = False
    
    ' 目次シートの取得（なければ作成）
    On Error Resume Next
    Set wsIndex = ThisWorkbook.Worksheets(indexName)
    On Error GoTo 0
    
    If wsIndex Is Nothing Then
        Set wsIndex = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
        wsIndex.Name = indexName
    End If
    
    ' 目次の初期化
    wsIndex.Cells.Clear
    wsIndex.Range("A1").Value = "シート目次（クリックで移動）"
    wsIndex.Range("A1").Font.Bold = True
    wsIndex.Range("A1").Font.Size = 14
    wsIndex.Range("A2").Value = "No."
    wsIndex.Range("B2").Value = "シート名"
    wsIndex.Range("A2:B2").Font.Bold = True
    
    r = 3
    
    ' 全シートを一覧化（目次シートは除外）
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsIndex.Name Then
            
            wsIndex.Cells(r, 1).Value = r - 2
            wsIndex.Cells(r, 2).Value = ws.Name
            
            ' シートへ移動するハイパーリンクを作成
            ' ※ '#' はブック内リンクの意味
            wsIndex.Hyperlinks.Add _
                Anchor:=wsIndex.Cells(r, 2), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!A1", _
                TextToDisplay:=ws.Name
            
            r = r + 1
        End If
    Next ws
    
    ' 見た目を整える
    wsIndex.Columns("A").ColumnWidth = 5
    wsIndex.Columns("B").ColumnWidth = 35
    wsIndex.Range("A2:B" & r - 1).Borders.LineStyle = xlContinuous
    wsIndex.Activate
    wsIndex.Range("A1").Select
    
    Application.ScreenUpdating = True
    
    MsgBox "目次を作成しました。", vbInformation

End Sub