【Excel VBA】シートの一覧(ハイパーリンク付き)を作るマクロ

Excel VBA シートの一覧(ハイパーリンクつき)のマクロ

全てのシートの一覧を作りたい、欲を言えば一覧にはリンクも付けたい …という要望があったのでマクロを作成しました!

VBAでマクロを作るための準備

Module1を追加する

標準モジュールにModule1を追加します。

【VBAProjectを右クリック】→ 【挿入】 → 【標準モジュール】をクリックします。

そもそもこの画面なに?という方は、以下の記事でVBAを書き始める準備の方法をまとめているのでご覧ください。

Excel VBA マクロの作り方【Excel VBA】Excelマクロの始め方 ~簡単なサンプル付き~

Excel VBA: シートの一覧を作るマクロ(ハイパーリンク付き)

1. VBAのソースコードを書く

Module1を追加したら、以下のソースコードをコピぺしましょう。処理内容はソースのコメントに書いてあります。

ポイント
  • SHEETNAME_ITIRANという定数に”一覧”シートの名前を入れています
  • 2つのメソッドがあります、MakeItiran(”一覧”シート作成)とDeleteItiranSheet(”一覧”シートの削除)
  • 実行するマクロはMakeItiranです

copyを押してソース全体をコピーします。

copy

Private Const SHEETNAME_ITIRAN = "一覧"

' 一覧シートを作成する(こちらのマクロを実行します)
Sub MakeItiran()
    
    ' "一覧"シートがある場合にはいったん削除する
    Call DeleteItiranSheet
    
    ' 1シート目に"一覧"シートを作成する
    Worksheets.Add Before:=Worksheets(1)
    ActiveSheet.Name = SHEETNAME_ITIRAN
    
    ' 一覧を作成する、テキスト:シート名、ハイパーリンク:各シートのA1セル
    ' 1シート目には"一覧"シートがあるため、2シート目から処理をする
    Dim sheetCount As Integer
    For sheetCount = 2 To Worksheets.Count
    
        Worksheets(SHEETNAME_ITIRAN).Hyperlinks.Add Anchor:= _
            Range("B" & sheetCount), _
            Address:="", _
            SubAddress:=Worksheets(sheetCount).Name & "!A1", _
            TextToDisplay:=Worksheets(sheetCount).Name
            
        Range("A" & sheetCount).Value = sheetCount - 1
        
    Next

End Sub

' 一覧シートを削除する
Sub DeleteItiranSheet()

    Dim ws As Worksheet
    For Each ws In Worksheets
    
        If ws.Name = SHEETNAME_ITIRAN Then
            
            ' "削除しますか?"の確認メッセージを非表示にする
            Application.DisplayAlerts = False
            
            ' "一覧"シートを削除する
            Worksheets(SHEETNAME_ITIRAN).Delete
            
            ' メッセージを表示する設定に戻す
            Application.DisplayAlerts = True
            
            ' 削除したら処理を終了する
            Exit Sub
            
        End If
    
    Next ws

End Sub

Module1にソースコードをコピーしたら、以下のようになります。(少し長くなっています)

2. 準備

シートの隣にある+を押してシートを追加します。シート数は適当でOK。

3. マクロを実行する

画面上部の【▶】を押して、MakeItiranを選択し、実行ボタンを押します

1シート目(1番左)に、シートが追加されて、シート名が一覧で表示されていることを確認します。

また、リンクがきちんと作成されていることも確認しましょう。

B2セルにあるSheet1をクリックすると、Sheet1のA1セルに移動します。

これで完了です!

手作業で作成してもいいのですが、シートが増えるたびに修正するのは面倒ですよね。VBAを書ければ、意外と簡単にマクロを作れます。

面倒くさい作業はどんどん自動化していきましょう!

 

↓ VBAを学びたい人におすすめの書籍です!