【Excel VBA】シート名の一覧を取得しハイパーリンク付きで一覧表示する

数多くあるシートの一覧を取得し、まとめたいときはないでしょうか。

けどそんな時に悩むことは、

ですよね。

今回は、そんなお悩みを解決する、

についてまとめます！

Excel内のシートのサイトマップをつくる

今回は抽出したシート一覧へハイパーリンクを付与して、該当するシートへスパスパ遷移できるようなものをVBAで実現したいと思います。

Excelブック内にある10シート分のシート名とA1列の値を取得し一覧化、その上でハイパーリンクを付与していきます。

A列のハイパーリンクをクリックするとシート間の移動ができます。

さらに各シートの中のセルの値を同時に取得するようにしてシート名だけではなくシート内にある値も引っ張ってきてデータをシート別に比較できるよう併せて一覧化をしたいと思います。

シート名の一覧を取得しハイパーリンク付きで一覧表示するVBA

シート名の一覧を取得しハイパーリンク付きで一覧表示するVBAのサンプルコードは以下の通りです。

Sub シート名一覧取得()

    Dim objSheetName As Worksheet
    Dim objSheetNameMT As Worksheet
    Dim i As Integer

    'シート一覧を出力するシートを指定します。
    Set objSheetNameMT = Sheets("一覧")
    '一覧シートの内容を削除します。
    objSheetNameMT.Columns("A:B").Clear
    i = 1
    
    'Sheetsから1シートづつ取得します。
    For Each objSheetName In Sheets
        '一覧シートはスキップします。
        If objSheetName.Name <> "一覧" Then
            'シート名を取得します。そして一覧シートへ出力します。
            objSheetNameMT.Cells(i, 1).Value = objSheetName.Name
            'A1セルの値を取得します。そして一覧シートへ出力します。
            objSheetNameMT.Cells(i, 2).Value = objSheetName.Range("A1")
            'シートへのハイパーリンクを設定します。
            objSheetNameMT.Hyperlinks.Add Anchor:=objSheetNameMT.Cells(i, 1), Address:="", SubAddress:="'" & objSheetName.Name & "'!A1", TextToDisplay:=objSheetName.Name
            
            i = i + 1
        End If
    Next
    
End Sub

VBAを設定する

VBAを設定していきましょう。

シート一覧を出力するシートを指定します。サンプルのシート名は”一覧”としています。

VBAを実装する

VBAを実装していきましょう。

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

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

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

④シート名「一覧」のシートを新規に作成します。

⑤リボンメニューからマクロの実行は可能ですが、便利なマクロを実行するボタンを入れておきましょう。
④で作成した「一覧」のシートのリボンメニューの「開発」タブ＞「挿入」＞左上のボタンをクリックします。

はい、挿入されました。ボタン名称は適宜変えてもらえばと思います。

このボタンに今回のマクロを実行できるよう指定します。

VBAを実行する

VBAを実行してみましょう。
設置したボタンを押下します。

はい！シート一覧が取得されましたね！

ハイパーリンクをクリックしてみます。
はい！シート間の移動もできました！

VBAの説明

一覧シートの内容を削除します。

Sheetsコレクションから1シートづつ取得します。

一覧シートはスキップします。

シート名を取得します。そして一覧シートへ出力します。

A1セルの値を取得します。そして一覧シートへ出力します。

シートへのハイパーリンクを設定します。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

今回の機能でより使いやすくなれば何よりです。