エクセルのシートが多くて探すのに時間がかかる…
リンク付きの目次シートを作りたいけど、シートが増えるたびにつくるのが大変…
エクセルのシートが増えると、どこに何があるのか分からなくなることがあります。そんなときに便利なのが目次シート。ただシートを追加・変更するたびに目次を作るのは、かなり手間がかかってしまいます。
そこで今回は、VBAを使ってリンク付きの目次を自動で作成する方法を紹介します。この目次シートがあれば、一目で全シートを把握でき、クリックひとつで各シートへ移動できます。
VBAコードの紹介はもちろん、ダウンロードするだけで使えるファイルも掲載しますので、手軽に目次を作って効率化を行ってください！
【結論】コピペで使える目次VBA
まず、目次VBAのコードを紹介します。
動作や使い方に関しては、後程紹介します。
Sub ListSheets()
Dim i As Integer
Dim columnIndex As Integer
Dim rowIndex As Integer
Range("A2:Z" & Rows.Count).ClearContents
columnIndex = 1
For i = 1 To Worksheets.Count
If Worksheets(i).Name <> ActiveSheet.Name Then
rowIndex = 2 + (i - 1) Mod 20
Cells(rowIndex, columnIndex) = Worksheets(i).Name
ActiveSheet.Hyperlinks.Add _
Anchor:=Cells(rowIndex, columnIndex), _
Address:="", _
SubAddress:="'" & Worksheets(i).Name & "'!A1"
If i Mod 20 = 0 Then
columnIndex = columnIndex + 1
End If
End If
Next i
End Sub
※当サイトで紹介している他のコピペコードは、コチラにまとめています！
【番外】そのまま使えるエクセルファイル
VBAが全く分からないという方のために、VBAを組み込んだエクセルファイルを用意しました。
このファイルにシートを追加するだけで、簡単に目次が作成できます。
VBAファイルはNoteにて販売しているので、設定なしですぐに使いたい方はこちらを活用ください。
目次VBAの動作と使い方
このVBAでは、目次シートに全シート名がリストアップされ、リンクが自動で作成されます。
ボタンをつければ押すだけで簡単に目次を更新できるので、新しいシートを追加してもすぐに反映可能です。
業務効率化におすすめ書籍『9選』はコチラから！
VBAの貼り付けと実行
エクセルの「開発」タブから「Visual Basic」エディタを開きます。そしてVBAコードを「標準モジュール」に貼り付けます。
「開発」を出す設定やコードの書き方は、VBAの基礎まとめのページで詳しく解説しています。こちらの記事もぜひ参考にしてみてください。
今回のコードについて
次に今回作成したコードを簡単に解説していきます。
なお、VBAの基礎についてはこの記事一つにまとめていますので、ぜひこちらも参考にしてみてください。
1. 宣言
Dim i As Integer
Dim columnIndex As Integer
Dim rowIndex As Integer
変数を定義して、VBAが適切に処理できるように準備します。
『変数』や『宣言』など専門的なワードが出てきていますが、詳しくは別記事で説明しています。興味がある方はぜひこちらも見てみてください。
2. 初期化
Range("A2:Z" & Rows.Count).ClearContents
シート情報をリストするために、出力範囲をクリアします。
3. ループ処理
For i = 1 To Worksheets.Count
…
Next i
ループ処理を使って、すべてのシートを対象に処理を行います。
このループ処理はVBAでの効率化で、とても有効なコードです。別のコードでもこのループ処理を使って効率化するVBAを紹介していますので、こちらもぜひ参考にしてみてください。
4. 自分のシートを除外
If Worksheets(i).Name <> ActiveSheet.Name Then
…
End If
目次を作成するシート自体はリストに含めないために、IF文で条件分岐を行います。このIF文も効率化に欠かせないコードです。
5. 改行の設定
rowIndex = 2 + (i - 1) Mod 20
…
If i Mod 20 = 0 Then
columnIndex = columnIndex + 1
End If
20シートごとに列を移動し、見やすく配置します。ここでもIF文を使って、20行が過ぎているかどうかを分岐しています。
6. 出力
Cells(rowIndex, columnIndex) = Worksheets(i).Name
シート名をセルに入力します。
7. ハイパーリンク作成
ActiveSheet.Hyperlinks.Add _
Anchor:=Cells(rowIndex, columnIndex), _
Address:="", _
SubAddress:="'" & Worksheets(i).Name & "'!A1"
セルにシート名を入力すると同時に、リンクを作成します。
+α. ボタンの作成
ボタンを配置すると操作が簡単になります。
ボタンの追加方法は以下の記事を参照してください。
さいごに
リンク付き目次の自動化は、関数では難しい処理でありながら、比較的簡単に実装できるVBAです。初心者向けで効率も上がるので、是非ともVBAでチャレンジしてみてほしい内容です。
今回紹介したようなVBAスキルは、一度身につければ一生使える武器となります。コピペも便利ですが、本で体系的に学ぶことで「自分でコードを書く力」を短期間で身につけられます。
以下の記事では、Kindle Unlimited（読み放題）で無料で読めるおすすめのVBA書籍を厳選して紹介しています。生成AIを前提とした、今までとは全く違ったVBA学習法も紹介しているので、是非チェックしてみてください！
