Excel 目次でシート名を一括表示するVBA

ワークシートの目次を作成する

まず、ハイパーリンクを関数を使って手動で設定する方法があります。ボクは長い間、この方法を採用していました。特に問題がなかったため、これで十分だと感じていました。しかし、ある日突然、扱いにくいシートが届けられました。シートの数は全部で約50ありました。

このシートにはデータが雑然としており、何がどこにあるのか把握するのが困難で、使い勝手が悪いものでした。さらに、このシートはクライアントが長年使用しているもので、セルの位置やシートの順序を変更することは禁じられていました。そのため、元のワークブックには手を加えず、コピーしたものにVBAを用いて目次を追加することに決めました。

Excelの目次を手動で作成する方法

10シートくらいならこれでもやれなくはないですが、まずはベタな手動での方法の説明です。

1. ワークブック下の「＋」記号（新しいシートを追加）をクリックして、新しいシートを追加します。　

2. 新しく追加したシートの名前をダブルクリックして、「目次」などの名前に変更します。

3. 目次シートのA列に、他のシートの名前をリストアップします。例えば、A1セルに「シート1」、A2セルに「シート2」と入力します。

4. 各シート名を選択し、右クリックして「ハイパーリンク」を選択します。表示されるダイアログで、「このドキュメント内の場所」を選択し、リンク先のシートを選んで「OK」をクリックします。

Excelの目次をVBAで自動作成する方法

次に、VBAコードで一発で目次を作成する方法です。

Sub 目次シートに全シートの目次作成()  
Dim A, i As Long, sh As Worksheet
    For Each sh In Worksheets
        sh.Visible = True
    Next   
  ReDim A(1 To Sheets.Count)  
  For i = 1 To Sheets.Count
    A(i) = Sheets(i).Name
  Next 
  Sheets.Add before:=Sheets(1)
  ActiveSheet.Name = "目次"
  Range("A1").Resize(UBound(A)) = WorksheetFunction.Transpose(A)
    i = 1
    For Each sh In Sheets
        ActiveSheet.Hyperlinks.Add Anchor:=Sheets("目次").Cells(i, 1), Address:="", SubAddress:="'" & sh.Name & "'!A1", TextToDisplay:=sh.Name
        i = i + 1
    Next  
    For Each sh In Worksheets
        sh.Visible = True
    Next
End Sub

このコードがあれば数秒で目次シートが作成できます。ぜひ使ってみてください。