シート名の一覧を取得し、リンク付きの目次を作成する方法
Excel VBAを使用すると、ワークブック内のすべてのシート名を取得し、自動で目次を作成することができます。目次には各シートへのリンクが設定されており、クリックすることで瞬時に移動できるようになります。
以下では、VBAを使用してシート名を取得し、リンク付きの目次を作成する方法を具体的に解説します。
VBAの作成と実行
- VBAは、[開発]→[VisualBasic]→[挿入]→[標準モジュール]→コードを貼り付け の順で作成します。
- 実行する際は、[開発]→[マクロ]→作成したマクロを選択して実行します。
リンク付きの目次を作成するVBAコード
このVBAコードを実行すると、新しく「目次」シートが作成され、ワークブック内の全シート名が一覧表示されます。それぞれのシート名には、クリックするとそのシートへジャンプするハイパーリンクが設定されます。
Sub CreateSheetIndex()
Dim ws As Worksheet
Dim indexSheet As Worksheet
Dim i As Integer
' 既存の「目次」シートを削除
On Error Resume Next
Application.DisplayAlerts = False
Sheets("目次").Delete
Application.DisplayAlerts = True
On Error GoTo 0
' 新しい「目次」シートを作成
Set indexSheet = ThisWorkbook.Sheets.Add
indexSheet.Name = "目次"
' 見出しを設定
indexSheet.Cells(1, 1).Value = "シート一覧"
indexSheet.Cells(1, 1).Font.Bold = True
' シート名の一覧を取得して目次を作成
i = 2
For Each ws In ThisWorkbook.Sheets
If ws.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コードの解説
On Error Resume Nextを使用し、既存の「目次」シートがある場合は削除する。Sheets.Addを使用して、新しく「目次」シートを作成する。For Each ws In ThisWorkbook.Sheetsを使用し、現在のワークブックのシートのみを対象に処理する。- ハイパーリンクの
SubAddressにReplace(ws.Name, "'", "''")を適用し、シート名にシングルクォートが含まれる場合でも正しく動作するようにする。 Columns("A").AutoFitで、列幅を自動調整し、見やすくする。
実行結果
このマクロを実行すると、「目次」シートが作成され、以下のように各シート名とハイパーリンクが一覧表示されます。
| シート一覧 |
|---|
| Sheet1 |
| Sheet2 |
| Sheet3 |
目次を自動更新する方法
シートの追加・削除に対応するため、WorkbookのOpenイベントを利用して、Excelファイルを開くたびに目次を更新することも可能です。
Private Sub Workbook_Open()
CreateSheetIndex
End Subまとめ
- VBAを使用すると、Excelのすべてのシート名を取得し、自動で目次を作成できる。
- 各シート名にはハイパーリンクが設定され、ワンクリックでシートに移動可能。
- 他のウィンドウで開いているブックのシートは目次に含めない。
- シート名に特殊文字（シングルクォート）が含まれていても正しくリンクを設定できる。
- Workbook_Openイベントを利用すると、ファイルを開くたびに目次を自動更新できる。