【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

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

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

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