「シートが増えすぎて、どこに何のシートがあるかわからない…」
「シート名を一覧にして、目次のように管理したい」
そんなときに便利なのが、ExcelVBAでブック内のシート名を自動で一覧にするマクロです。
この記事では、シート名の一覧（目次シート）をワンクリックで作成できるVBAコードをご紹介します。
動作イメージはこちら
目次
この記事はこんな方におすすめ
- シート数が多いブックを使っていて、探すのが大変な方
- シートの一覧（目次）を自動で作りたい方
- クリックで各シートにジャンプできる画面を作りたい方
- ExcelVBAで実務に使えるマクロを覚えたい初心者の方
シートの確認
今回は、下のように複数のシートを含むブックを想定します。
「営業」「経理」「事務」「総務」
このブックに対して、「シート一覧」シートを自動で作成し、
その中にシート名と、各シートへのハイパーリンクを一覧表示していきます。
VBAコード
ブック内のシート名を一覧にして目次シートを作るコード
Sub CreateSheetIndex()
Dim indexWs As Worksheet ' シート一覧（目次）用のシート
Dim ws As Worksheet ' ループ用の各シート
Dim rowIndex As Long ' 書き込み行
' 既に「シート一覧」シートがあるか確認
On Error Resume Next
Set indexWs = Worksheets("シート一覧")
On Error GoTo 0
' なければ新規作成、あれば中身をクリア
If indexWs Is Nothing Then
Set indexWs = Worksheets.Add(Before:=Worksheets(1))
indexWs.Name = "シート一覧"
Else
indexWs.Cells.ClearContents
End If
' 見出し行を作成
indexWs.Range("A1").Value = "No"
indexWs.Range("B1").Value = "シート名"
indexWs.Range("C1").Value = "ジャンプ"
rowIndex = 2
' ブック内の全シートをループ
For Each ws In ThisWorkbook.Worksheets
' 「シート一覧」シート自身は除外
If ws.Name <> indexWs.Name Then
' 通し番号
indexWs.Cells(rowIndex, 1).Value = rowIndex - 1
' シート名
indexWs.Cells(rowIndex, 2).Value = ws.Name
' C列にハイパーリンクを作成（クリックで対象シートA1セルへ）
indexWs.Hyperlinks.Add _
Anchor:=indexWs.Cells(rowIndex, 3), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:="移動"
rowIndex = rowIndex + 1
End If
Next ws
' 見た目を整える
With indexWs
.Columns("A:C").AutoFit
.Rows(1).Font.Bold = True
.Range("A1:C1").Interior.Color = RGB(221, 235, 247)
End With
MsgBox "シート一覧を作成しました！", vbInformation
End Sub
コードのポイント解説
① 「シート一覧」シートの作成／再利用
On Error Resume Next
Set indexWs = Worksheets("シート一覧")
On Error GoTo 0
If indexWs Is Nothing Then
Set indexWs = Worksheets.Add(Before:=Worksheets(1))
indexWs.Name = "シート一覧"
Else
indexWs.Cells.ClearContents
End If
Worksheets("シート一覧")で、既にあるかどうかを確認- なければ
Worksheets.Addで新しく作成し、Nameでシート名を設定 - あれば中身だけ
ClearContentsで削除して再利用
これにより、マクロを何度実行しても「シート一覧」が増え続けないようにしています。
② ブック内の全シートを1枚ずつ処理
For Each ws In ThisWorkbook.Worksheets
...
Next ws
ThisWorkbook.Worksheetsは「このブックの全シート」の集まりFor Each ～ Nextで、1枚ずつ順番に処理します
その中で、「シート一覧」自身だけは除外しています。
If ws.Name <> indexWs.Name Then
' 一覧に追加
End If
③ ハイパーリンクでジャンプできる「目次」にする
indexWs.Hyperlinks.Add _
Anchor:=indexWs.Cells(rowIndex, 3), _
Address:="", _
SubAddress:="'" & ws.Name & "'!A1", _
TextToDisplay:="移動"
Hyperlinks.Add…… ハイパーリンクを追加する命令Anchor…… どのセルにリンクを貼るか（ここではC列のセル）Address…… 外部ファイルなどのパス（今回は空文字）SubAddress…… 「ブック内のどこのセルに飛ぶか」（シート名＋セル番地）TextToDisplay…… セルに表示する文字（ここでは「移動」）
これにより、「移動」という文字をクリックすると、そのシートのA1セルにジャンプできるようになります。
④ 見た目の調整（AutoFit や見出し色）
With indexWs
.Columns("A:C").AutoFit
.Rows(1).Font.Bold = True
.Range("A1:C1").Interior.Color = RGB(221, 235, 247)
End With
AutoFit…… 列幅を自動調整して、文字がちょうど収まるように- 見出し行は太字＋背景色を付けて、一覧を見やすく
細かい装飾部分なので、好みに合わせて自由に変更してOKです。
マクロ実行
マクロを実行するまでの手順は、他の記事と同じ流れです。
- Excelで対象ブックを開く
- Alt + F11 キーでVBE（VBAエディタ）を開く
- 「挿入」→「標準モジュール」をクリック
- 表示されたコード画面に、先ほどの
CreateSheetIndexマクロを貼り付ける - ブックを「マクロ有効ブック（.xlsm）」として保存
- Excelに戻り、「開発」タブ →「マクロ」→
CreateSheetIndexを選択して「実行」
実行すると、新しく「シート一覧」シートが先頭に作成され、
ブック内の全シート名と「移動」リンクが一覧表示されます。
以降、シートを増やしたり削除したりした場合でも、
もう一度マクロを実行すれば、最新の構成に合わせて目次シートを作り直すことができます。
まとめ：シートが多いブックには「目次マクロ」が便利
今回は、ExcelVBAでブック内のシート名を自動で一覧にするマクロをご紹介しました。
シート数が多くなるほど、どこに何があるか分かりづらくなりますが、
目次シート（シート一覧）があるだけで、ブック全体がぐっと使いやすくなります。
- ブック内の全シート名を自動取得
- シート一覧用のシートを自動作成・更新
- ハイパーリンクで各シートへワンクリックジャンプ
「実務でよくあるちょっとした不便」を解消できるのがVBAの強みです。
ぜひこのマクロを、ご自身のブックに組み込んでみてください。
「シート一覧に他の情報も入れたい」「特定のシートだけを対象にしたい」など、
カスタマイズの相談もあれば、ぜひコメントで教えてください。
コメント