【Excel VBA】エクセルシート名の一覧を抽出しハイパーリンク付きで一覧表示する

今回はエクセルシート名の一覧を抽出し、ハイパーリンク付きで一覧表示させる方法を取り上げてみたいと思います。

エクセル内のシートのサイトマップをつくる

ブックでシートの数が数十件と増えてきたときはシートの一覧を取得しサイトマップ的に表示をさせたいことがある場合はないでしょうか。
今回は抽出したシート一覧へハイパーリンクを付与して、該当するシートへスパスパ遷移できるようなものをVBAで実現したいと思います。
さらに各シートの中のセルの値を同時に取得するようにしてシート名だけではなくシート内にある値も引っ張ってきてデータをシート別に比較できるよう併せて一覧化をしたいと思います。

利用するオブジェクトは「Sheets」オブジェクトです。

Sheets.<メソッド>

サポートしているメソッドは以下となります。

例えば作業中のブックの最後のシートの前に新しいワークシートを追加する場合は以下のようになります。

ActiveWorkbook.Sheets.Add(Before:=Worksheets(Worksheets.Count))

今回のマクロ

今回はサンプルでエクセルブック内にある10シート分のシート名とA1列の値を取得し一覧化、その上でハイパーリンクを付与してみたいと思います。

マクロは以下の通りです。

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")
            
            'シートへのハイパーリンクを設定します。
            ActiveSheet.Hyperlinks.Add Anchor:=objSheetNameMT.Cells(i, 1), Address:="", SubAddress:="'" & objSheetName.Name & "'!A1", TextToDisplay:=objSheetName.Name
            
            i = i + 1
        End If
    Next
    
End Sub

マクロを登録しましょう

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

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

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

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

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

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

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

⑥設置したボタンを押下します。
はい!シート一覧が挿入されましたね!

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

いかがでしょうか。
今回はシートの掌握方法としてのご紹介ですが、そもそもシートの入れすぎは管理が難しく、見落としの原因となりますのでデータの管理方法を見直した方がよいかもしれません。
今回の機能で見落とし防止や管理のしやすさが向上できれば何よりです。

今後も用途別にいろいろな使い方をご紹介したいと思います。

コメントを残す

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