【Excel VBA】ハイパーリンク付きシート名一覧を一瞬で作成する方法!

エクセルのシートの量が多すぎて、たどり着かないことってないですか?

仕事で手順書をエクセルで作る場合があるのですが、

とてつもないシートの量になるんですよね。

多い時では100シート以上。。。

で、存在するシートすべてを記載したハイパーリンク付き目次を作らせたのですが、一つ作らすのに半日かかったりしたんですね。

これは非常に効率がわるい!!

と思い、VBAの小技でサクっとハイパーリンク付きのシート一覧を瞬間作成したいとおもいます。

準備

まずはサンプルでたくさんのシートが挿入されているブックを用意します。
今回のシートの数は8個ですので、目次も8シート分できればOKということになります。

で、まだ目次シートはありません。

ではさっそくやってみましょう。

 



ハイパーリンク付きシート名一覧を一瞬で作成するマクロ

今回の目次ををマクロにした場合は以下のようになります。

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("一覧")

 



さいごに

いかがでしょうか。
今回作成したマクロは簡易的に作ったもので、エラーハンドリングや目次シート自身が目次一覧に含まれていて改良の余地はあります。

しかしマクロ作成にかかった時間はほんのわずかです。

それで半日かけていた作業があっという間に片付いたので、不完全でも全然OKでした。

目的によって完成度を見極めるとよいかもしれませんね。



コメントを残す

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