エクセルのシートが多くて探すのに時間がかかる…
リンク付きの目次シートを作りたいけど、シートが増えるたびにつくるのが大変…
エクセルのシートが増えると、どこに何があるのか分からなくなることがあります。そんなときに便利なのが目次シート。ただシートを追加・変更するたびに目次を作るのは、かなり手間がかかってしまいます。
そこで今回は、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を組み込んだエクセルファイルを用意しました。
このファイルにシートを追加するだけで、簡単に目次が作成できます。
※ウイルスではもちろんありませんが、マクロ付きのxlsmファイルのため、使用は自己責任でお願いします。
目次VBAの動作と使い方
このVBAでは、目次シートに全シート名がリストアップされ、リンクが自動で作成されます。
ボタンをつければ押すだけで簡単に目次を更新できるので、新しいシートを追加してもすぐに反映可能です。
VBA貼り付けの方法
ここからは、冒頭のVBAコードを使用する方法を紹介します。
1. 開発タブの表示
まず、エクセルのタブに「開発」が表示されていない方は、初期設定が必要です。
5分程度で完了しますので、以下のページを参考に開発タブを表示してください。
2. VBAの記入と実行
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でチャレンジしてみてほしい内容です。
なおこのブログでは、作業効率化に関する情報を多数紹介しています。他の記事もぜひチェックして効率化してスキルアップをしてください！
コメント