【VBA】Excelの全シートを目次にリンクする方法

「Excelのシートが多くなると、必要なシートを探すのが大変！」

そんな悩みを解決する方法が、VBAを使った目次シートの作成です。

本記事では、Excelの全シートを自動で一覧化し、目次としてまとめる方法をわかりやすく解説します。

ハイパーリンク機能に自動対応するテクニックもご紹介するので、業務効率化に役立つこと間違いなしです！

本記事で作成する目次シートのイメージ

本記事で作成する目次シートのイメージは下記画像の通りとなります。

シートA、シートB、シートCというシートがあった時、目次というシートのA列の2行目以降に「シートA、シートB、シートC」を順に書き込んでいきます。

また１行目にはタイトルとして「シート一覧」と書き込みます。

シート名や書き込む列を変更する手順についても紹介します。

また、より便利に使用できるようにするために目次をハイパーリンクにする方法も記載します。

全シート名を一覧表示するVBAコード

目次というシートのA列に他のシート名を書き込むようにするVBAは以下のようになります。

もし目次というシートが無ければ目次シートを作成してから、他シートの一覧を書き込んでいきます。

Public Sub CreateMokuji()
    Dim ws As Worksheet
    Dim targetSheet As Worksheet
    Dim i As Long
    
    Const TARGET_SHEET_NAME As String = "目次"
    Const TARGET_COLUMN As String = "A"
    
    ' 目次シートを取得
    On Error Resume Next
    Set targetSheet = ThisWorkbook.Sheets(TARGET_SHEET_NAME)
    On Error GoTo 0
    
    ' 目次シートが存在しない場合は作成
    If targetSheet Is Nothing Then
        Set targetSheet = ThisWorkbook.Sheets.Add
        targetSheet.Name = TARGET_SHEET_NAME
    End If
    
    ' "目次"シートをクリア
    targetSheet.Columns(TARGET_COLUMN).Clear
    
    ' 見出しを設定
    targetSheet.Range(TARGET_COLUMN + "1").Value = "シート一覧"
    
    ' 他のシート名を取得してA列に記載
    i = 2
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "目次" Then
            targetSheet.Cells(i, 1).Value = ws.Name
            i = i + 1
        End If
    Next ws
    
    ' 列の幅を自動調整
    targetSheet.Columns(TARGET_COLUMN).AutoFit
End Sub

Const TARGET_SHEET_NAME As String = "目次"
Const TARGET_COLUMN As String = "A"

上記のConstでシート名と目次を記載する列を定義しています。

もし「一覧というシートのC列」に書き題したい場合には、以下のように定義を変更すれば実現できます。

Const TARGET_SHEET_NAME As String = "一覧"
Const TARGET_COLUMN As String = "C"

便利度アップ！ハイパーリンク付き目次を作るVBAコード

前項で目次シートにシート名の一覧を表示することができました。

ただ、シートが増えてくると目次があったとしても該当のシートを探して開くことが面倒になってきます。

そこで目次に各シートへのハイパーリンクを貼ることで、一瞬で該当のシートを開けるようにします。

目次のシート名をハイパーリンクにするソースは以下の通りになります。

Public Sub CreateMokuji()
    Dim ws As Worksheet
    Dim targetSheet As Worksheet
    Dim i As Long
    
    Const TARGET_SHEET_NAME As String = "目次"
    Const TARGET_COLUMN As String = "A"
    
    ' 目次シートを取得
    On Error Resume Next
    Set targetSheet = ThisWorkbook.Sheets(TARGET_SHEET_NAME)
    On Error GoTo 0
    
    ' 目次シートが存在しない場合は作成
    If targetSheet Is Nothing Then
        Set targetSheet = ThisWorkbook.Sheets.Add
        targetSheet.Name = TARGET_SHEET_NAME
    End If
    
    ' "目次"シートをクリア
    targetSheet.Columns(TARGET_COLUMN).Clear
    
    ' 見出しを設定
    targetSheet.Range(TARGET_COLUMN + "1").Value = "シート一覧"
    
    ' 他のシート名を取得してA列に記載
    i = 2
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> TARGET_SHEET_NAME Then
            ' ハイパーリンクを追加
            targetSheet.Hyperlinks.Add Anchor:=targetSheet.Cells(i, 1), _
                                       Address:="", _
                                       SubAddress:="'" & ws.Name & "'!A1", _
                                       TextToDisplay:=ws.Name
            i = i + 1
        End If
    Next ws
    
    ' 列の幅を自動調整
    targetSheet.Columns(TARGET_COLUMN).AutoFit
End Sub

変更点のポイント

ハイパーリンクの追加として、Hyperlinks.Addメソッドを使用しています。

targetSheet.Hyperlinks.Add Anchor:=targetSheet.Cells(i, 1), _
                           Address:="", _
                           SubAddress:="'" & ws.Name & "'!A1", _
                           TextToDisplay:=ws.Name

Anchor: ハイパーリンクを挿入するセル
Address: 外部リンクを使用する場合のURL。今回はシート内リンクなので空文字。
SubAddress: ハイパーリンク先（例: ‘シート名’!A1）。
TextToDisplay: ハイパーリンクに表示するテキスト（例: シート名）

テキストやリンク先を変更したい場合は上記のパラメータを修正することで変更できます。

例えばA1ではなく、B2を開きたいときはSubAddressのA1の部分をB2とします。