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

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

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

・Excelシート名の一覧を取得する方法がわからない
・Excelシート名の一覧を取得した上でハイパーリンクで該当シートへ移動したいが方法がわからない

ですよね。

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

Excelシート名の一覧を取得し、ハイパーリンク付きで一覧表示させる方法

についてまとめます!

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を設定していきましょう。

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

Set objSheetNameMT = Sheets("一覧")

VBAを実装する

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

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

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

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

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

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

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

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

VBAを実行する

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

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

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

VBAの説明

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

objSheetNameMT.Columns("A:B").Clear

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

さいごに

いかがでしょうか。

今回は、

Excelシート名の一覧を取得し、ハイパーリンク付きで一覧表示させる方法

についてまとめました。

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



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

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





タカヒロ

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

結果は意外でした…

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




コメントを残す

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