一瞬でExcelの目次シートを作成する方法！特定シートの移動/戻りも！

そんなときに困ることは、

ですね。

そこでおすすめなのが、ブックの先頭に「目次シート」を置くことです。

目次から各シートへ飛べて、各シートからは目次へ戻れる。この行き来ができるだけで、ブックの使い勝手は驚くほど変わります。

今回はそんなお悩みを解決する、

についてまとめます！

完成イメージ

マクロを実行すると、ブックの先頭に「目次」シートが作られ、5つのシートへのリンクが並びます。

– 「▶ 開く」をクリックすると、そのシートへ移動
– 「内容」の列には、シートの役割をメモとして記載
– 非表示シートは自動で除外（作業用のシートを載せない）

さらに、各シートのB1セルには「← 目次へ戻る」リンクが入ります。

これで「目次 → 各シート → 目次」の行き来がワンクリックでできるようになります。

目次シートがあると何が変わるのか

STEP1. サンプルのブックを用意する

まずは練習用に、次の6シートを作ってください（1枚は非表示にします）。

売上データの表はこちらです（そのまま貼り付けられます）。

「作業用(非表示)」シートは、シートタブを右クリック→「非表示」にしておきましょう。

STEP2. 目次シートを作成するマクロを貼る

Alt+F11 でVBE（Visual Basic Editor）を開き、「挿入」→「標準モジュール」で次のコードを貼り付けます。

Sub 目次シートを作成する()
    Dim ws As Worksheet, toc As Worksheet
    Dim r As Long

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ' ① 既存の目次シートを削除する（何度実行しても重複しない）
    On Error Resume Next
    Worksheets("目次").Delete
    On Error GoTo 0

    ' ② 先頭に目次シートを挿入する
    Set toc = Worksheets.Add(Before:=Worksheets(1))
    toc.Name = "目次"
    toc.Range("A1").Value = "目次"
    toc.Range("A1").Font.Size = 18
    toc.Range("A1").Font.Bold = True
    toc.Range("A4:C4").Value = Array("シート名", "移動", "内容")
    toc.Range("A4:C4").Font.Bold = True

    ' ③ シートを1枚ずつ目次に登録する
    r = 5
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "目次" And ws.Visible = xlSheetVisible Then
            toc.Cells(r, 1).Value = ws.Name
            toc.Hyperlinks.Add Anchor:=toc.Cells(r, 2), Address:="", _
                SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:="▶ 開く"

            ' 各シートに目次へ戻るリンクを張る
            ws.Range("B1").Value = "← 目次へ戻る"
            ws.Hyperlinks.Add Anchor:=ws.Range("B1"), Address:="", _
                SubAddress:="'目次'!A1", TextToDisplay:="← 目次へ戻る"

            r = r + 1
        End If
    Next ws

    toc.Range("A4:C" & (r - 1)).Borders.LineStyle = 1
    toc.Columns("A:C").AutoFit
    toc.Activate

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    MsgBox (r - 5) & "件のシートを目次に登録しました"
End Sub

コードのポイント

STEP3. マクロを実行する

VBEで F5 を押すか、Excelに戻って「開発」タブ→「マクロ」から実行します。

「5件のシートを目次に登録しました」と表示されれば成功です。

実行結果を実際に確認したものがこちらです。

STEP4. リンクで移動してみる

目次シートの「▶ 開く」をクリックすると、そのシートへ移動します。

移動先のシートでは、B1セルの「← 目次へ戻る」をクリックすれば、すぐに目次へ戻れます。

タブを探してクリックする手間がなくなるので、シートが多いブックほど効果を実感できますね。

STEP5. 特定のシートを目次から除外する

「マスタ」や「作業用」のように目次に載せたくないシートは、条件で除外できます。

Sub 除外シートを指定して目次を作成する()
    Dim ws As Worksheet, toc As Worksheet
    Dim r As Long
    Dim 除外リスト As String

    ' 目次に載せないシート名をカンマ区切りで指定する
    除外リスト = ",マスタ,作業用,"

    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets("目次").Delete
    On Error GoTo 0

    Set toc = Worksheets.Add(Before:=Worksheets(1))
    toc.Name = "目次"
    toc.Range("A1").Value = "目次"
    toc.Range("A1").Font.Bold = True

    r = 3
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "目次" Then
            If ws.Visible = xlSheetVisible Then
                If InStr(除外リスト, "," & ws.Name & ",") = 0 Then
                    toc.Cells(r, 1).Value = ws.Name
                    toc.Hyperlinks.Add Anchor:=toc.Cells(r, 1), Address:="", _
                        SubAddress:="'" & ws.Name & "'!A1", TextToDisplay:=ws.Name
                    r = r + 1
                End If
            End If
        End If
    Next ws

    toc.Columns("A").AutoFit
    Application.DisplayAlerts = True
    MsgBox (r - 3) & "件を目次に登録しました（除外指定を反映）"
End Sub

補足すると、非表示シートは何もしなくても自動で除外されます。

「見せたくないシートは非表示にする」という運用と組み合わせるのが一番カンタンですね。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、うまく動かないこともあるかもしれません。

よくあるつまずきと対処法をまとめました。

よくある間違い

エク短流：シート名を取得してから目次を作る

「どんなシートがあるか分からない」ときは、先にシート名を一覧にすると安全です。

Sub シート名を一覧にする()
    Dim ws As Worksheet, i As Long

    i = 2
    With ActiveSheet
        .Range("A1").Value = "No"
        .Range("B1").Value = "シート名"
        .Range("C1").Value = "表示状態"
        For Each ws In ThisWorkbook.Worksheets
            .Cells(i, 1).Value = i - 1
            .Cells(i, 2).Value = ws.Name
            .Cells(i, 3).Value = IIf(ws.Visible = xlSheetVisible, "表示", "非表示")
            i = i + 1
        Next ws
        .Range("A1:C" & (i - 1)).Borders.LineStyle = 1
        .Columns("A:C").AutoFit
    End With

    MsgBox ThisWorkbook.Worksheets.Count & "枚のシートがあります"
End Sub

これでシートの一覧と表示状態が一度に分かります。

目次を作る前に一度実行しておけば、除外したいシートの当たりを付けられますね。

タカヒロ

目次シートは一度マクロにしてしまえば、シートを追加するたびに実行し直すだけです。
「作ったときだけ」ではなく何度でも貼り直せる形にしておくのが、形骸化させないコツですよ。

さいごに

今回は、Excelで目次シートを一瞬で作成する方法をまとめました。

シートが増えてきたブックほど、目次があるだけで使いやすさが変わります。

外部に資料を提出するときの体裁も整いますので、まずは今回のサンプルで一度試してみてください。

最後までお読みいただきありがとうございました。