シート名を取得しリンク付きの目次シートを作成する方法！ VBAで自動生成 | EXCELトピックス

シート名の一覧を取得し、リンク付きの目次を作成する方法

Excelでシート数が多くなると、目的のシートを探すのに時間がかかることがあります。

VBAを使えば、ブック内のシート名を自動で一覧化し、クリックすると各シートへ移動できるリンク付きの目次シートを作成できます。

この記事では、シート名の一覧を取得し、リンク付きの目次を作成するVBAの作成方法を解説します。

作成する目次のイメージ

マクロを実行すると、新しく「目次」シートが作成され、各シート名が一覧表示されます。

シート一覧
Sheet1
Sheet2
Sheet3

一覧のシート名にはハイパーリンクが設定されるため、クリックするだけで対象シートへ移動できます。

VBAを入力する場所

まず、VBAコードを標準モジュールに貼り付けます。

1. 「開発」タブをクリックします。
2. 「Visual Basic」をクリックします。
3. VBAエディターで「挿入」→「標準モジュール」を選択します。
4. 表示されたコード画面に、後述のVBAコードを貼り付けます。

※「開発」タブが表示されていない場合は、Excelのオプションから開発タブを有効にしてください。

リンク付きの目次を作成するVBAコード

次のコードを実行すると、既存の「目次」シートを作り直し、ブック内の各シートへのリンクを一覧表示します。

Sub CreateSheetIndex()

    Dim ws As Worksheet
    Dim indexSheet As Worksheet
    Dim i As Long

    ' 既存の「目次」シートを削除
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets("目次").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    ' 新しい「目次」シートを先頭に作成
    Set indexSheet = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
    indexSheet.Name = "目次"

    ' 見出しを設定
    indexSheet.Cells(1, 1).Value = "シート一覧"
    indexSheet.Cells(1, 1).Font.Bold = True

    ' シート名の一覧を取得してリンクを作成
    i = 2

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> indexSheet.Name Then
            indexSheet.Hyperlinks.Add _
                Anchor:=indexSheet.Cells(i, 1), _
                Address:="", _
                SubAddress:="'" & Replace(ws.Name, "'", "''") & "'!A1", _
                TextToDisplay:=ws.Name

            i = i + 1
        End If
    Next ws

    ' 列幅を調整
    indexSheet.Columns("A").AutoFit

    MsgBox "目次シートを作成しました。", vbInformation

End Sub

マクロを実行する方法

1. Excelに戻ります。
2. 「開発」タブをクリックします。
3. 「マクロ」をクリックします。
4. CreateSheetIndexを選択します。
5. 「実行」をクリックします。

実行すると、ブックの先頭に「目次」シートが作成されます。

コードの解説

• ThisWorkbook.Worksheets("目次").Delete：既存の目次シートを削除します。
• ThisWorkbook.Worksheets.Add：新しい目次シートを作成します。
• For Each ws In ThisWorkbook.Worksheets：現在のブック内にあるすべてのシートを順番に処理します。
• indexSheet.Hyperlinks.Add：各シートへのハイパーリンクを作成します。
• Replace(ws.Name, "'", "''")：シート名にシングルクォートが含まれている場合でもリンクが壊れないようにしています。
• Columns("A").AutoFit：シート名が見やすいように列幅を自動調整します。

ファイルを開くたびに目次を自動更新する方法

シートを追加・削除するたびに目次を作り直すのが面倒な場合は、ブックを開いたときに自動で目次を更新できます。

次のコードは、ThisWorkbookに貼り付けます。

Private Sub Workbook_Open()
    CreateSheetIndex
End Sub

この設定を行うと、ブックを開いたタイミングでCreateSheetIndexが実行され、目次が更新されます。

注意点

• このコードは、マクロを保存できる.xlsm形式で保存する必要があります。
• 既存の「目次」シートは削除して作り直されます。目次シートに手入力した内容がある場合は消えるため注意してください。
• 対象になるのはThisWorkbook、つまりこのマクロが保存されているブック内のシートです。
• ブックを開くたびに自動更新する場合、マクロの有効化が必要です。

まとめ

• VBAを使うと、ブック内のシート名一覧を自動で取得できる。
• Hyperlinks.Addを使うと、各シートへ移動できるリンク付き目次を作成できる。
• シート名にシングルクォートが含まれていても、Replaceで対応できる。
• Workbook_Openを使えば、ファイルを開くたびに目次を自動更新できる。
• 目次シートを作り直す処理では、既存の目次シートの内容が削除される点に注意する。