【Excel VBA】ハイパーリンク付き目次を自動で作成する方法!

Excelのシートの量が多すぎて、目的のシートへ辿りつかないことってないでしょうか。

例えば週次や月単位でシートを作成してそれが何年分もたまってしまっている場合などです。

タカヒロ
タカヒロ
100シート以上あるExcelファイルを見た時はうんざりしました…

そんな時に便利なのは、そんなシートを一覧にしたハイパーリンク付き目次ですね。

ただ、こんな時に悩むことは

・Excelファイルの目次をつくるのに時間がかかる
・ハイパーリンクをつける作業が面倒
・ハイパーリンクのリンク先が間違っていることがある
・シート名を変更したり、追加するたびに目次も更新しなければならない

ですね。

今回はそんなお困りごとを一瞬で解決する、

Excel VBAを利用したハイパーリンク付きのシート目次を自動で瞬間作成する方法
をご紹介したいと思います。

目次作成のイメージ

今回はExcelに目次シートを追加し、各シートのシート名とハイパーリンクを一覧化させていきます。

画像の赤枠に目次シートを挿入し、その中にハイパーリンク付き目次を挿入するVBAを加え、

VBAを実行すると

ハイパーリンク付き目次が自動的に作成されます!

カンタンですね!

目次作成対象のExcelファイルを準備する

まずはサンプルで複数のシートが挿入されているExcelファイルを用意します。

今回サンプルで用意したシートの数は8個となります。

シートの数は任意でかまいませんので、検証用にExcelファイルを用意していただければと思います。

次はVBAを実装してみましょう。

 

ハイパーリンク付きシート名一覧を一瞬で作成するマクロ

今回のマクロは以下のようになります。

Sub ハイパーリンク付き目次作成()
    Dim objSheet As Worksheet
    Dim objSheetMT As Worksheet
    Dim i As String

    'アクティブシートへシートを追加
    ActiveWorkbook.Sheets.Add
    
    '追加したシートをリネーム
    ActiveSheet.Name = "一覧"
    
    Set objSheetMT = Sheets("一覧")
    
    objSheetMT.Columns("A:E").Clear
    
    i = 2
    
    '全シートループ
    For Each objSheet In Sheets
        'シート名を出力
        objSheetMT.Cells(i, 1) = objSheet.Name

        
        'シートへのハイパーリンクを設定
        ActiveSheet.Hyperlinks.Add Anchor:=objSheetMT.Cells(i, 1), Address:="", SubAddress:="'" & objSheet.Name & "'!A1", TextToDisplay:=objSheet.Name
        
        i = i + 1
    Next
    
End Sub

アクティブブックに対して目次シート「一覧」を挿入し、

続いてブックに存在するすべてのシート名を抽出し、目次シート「一覧」へ入力させます。

さらに入力されたシート名へハイパーリンクを挿入していく処理内容となっています。

マクロを登録する

Alt + F11を押下してVBAのコンソールを開きます。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、挿入 > 標準モジュールを選択します。

③右ペインのウインドウに上記のマクロを入力します。

はい、ここで登録完了です。

マクロを実行する

続いて登録したマクロを実行し、目次シートへハイパーリンクつきシート名一覧を出力してみます。

①マクロを登録したブック以外で、目次を挿入したいブックを選択します。

②VisualBasicEditerを選択しマクロ実行ボタンを押下します。

目次シート「一覧」が追加され、入力された各シート名にハイパーリンクが付与されましたね!

別のサンプルExcelファイルを対象にやってみましょう。
次は11シート用意しました。

VisualBasicEditerを選択しマクロ実行ボタンを押下します。

はい!
11シート分が問題なく出力されました!

なお、目次シート名の「一覧」ですが、こちらの名前は自由に変更できます。

変える場合には以下"一覧"の箇所を変更してください。

ActiveSheet.Name = “一覧”

Set objSheetMT = Sheets(“一覧”)

 

さいごに

いかがでしょうか。

今回は、
Excel VBAを利用したハイパーリンク付きのシート目次を自動で瞬間作成する方法
をご紹介しました。

これまで何時間もかけていた作業がワンクリックで片付きますので、とても時間短縮になりましたね。

さらに他のExcelファイルに対しても適用ができますので、

ぜひアレンジして活用いただければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。





タカヒロ

ドラッカーの名言「強みを生かす」の自分の「強み」をツールでサクッと診断してみました。

結果は意外でした…

ストレングスファインダー診断結果公開!NGな点もまとめ!




コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です