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

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

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

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

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

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

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

ですね。

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

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

目次作成のイメージ

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

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

VBAを実行すると

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

カンタンですね!

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

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

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

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

次は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を選択しマクロ実行ボタンを押下します。

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

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

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

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

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

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

ActiveSheet.Name = “一覧”

Set objSheetMT = Sheets(“一覧”)

 

さいごに

いかがでしょうか。

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

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

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

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



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

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



タカヒロ

 
タカヒロ  
Excelの基礎がスルスル入ってくるAmazonランク1位の優良Excel本です。
 
この基礎本と応用技が多いエク短と組み合わせれば、短時間で誰よりもExcelが使いこなせるかと思います。
 
2022/1/21(金)23: 59までAmazon限定の「厳選ショートカットキー」DL特典アリです。 >詳細を見てみる 

コメントを残す

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