エクセルで目次をつくるハイパーリンクの設定方法！【マクロ自動化】

Sponsored Link

どんな業務であれ、Excelで資料をつくることは日常的にあります。なんでもかんでもExcelで作ろうとするのは無理があるように思えてならないのですが、便利すぎるツールなので致し方ないですね。

Excelファイル(ブック)にシートが増えてきたときに困るのが、必要な情報がどのシートに書かれていたか分からなくなることだと思います。Wordの資料であれば、書式を適切に設定すれば目次を作ることは簡単ですが、Excelではそうはいきません。

今回の記事では、Excelのハイパーリンクの機能を利用して、各シートに1クリックで移動することができるシート名の一覧(エクセルの目次)をつくる方法を紹介します。

Sponsored Link

エクセルでシートを検索するには？
エクセルでシート名の一覧表を作る
シート名の一覧表にハイパーリンクを設定する
1. 関連

エクセルでシートを検索するには？

Excelのシートは画面下部のインデックスから選んで表示を切り替えることができますが、シートが沢山ある場合、表示したいシートを探すのも一苦労です。

Excelの左下の領域を右クリックするとシートの選択ダイアログが表示されるので、そこから表示したいシートを選ぶことができます。

ただし、このダイアログにはシート名が一度に20件までしか表示されないので、シートが21枚以上ある場合には、スクロールする必要があります。

その上、困ったことにExcelにはシート名を検索する方法がありません。

シートが21枚以上あるような状態では、たいていの場合シート名を検索したくなるものですが、その手段がないのです。。。

そこでまず、シート名の一覧表をつくることを考えます。

Sponsored Link

エクセルでシート名の一覧表を作る

一覧表といっても、ここで紹介するマクロで作るのは、単純にシート名を箇条書きしたものですが、このシート名の一覧表が有るのと無いのとでは、資料の検索性が大きく変わります。

まずは、ソースコードを掲載します。

'------------------------------
'シート名の一覧をつくる
'------------------------------
Sub ListUpSheetName()
    Dim ws As Worksheet
    Dim tmpRow As Long
    
    'ブックの先頭にシートを追加する
    Sheets.Add Before:=Worksheets(1)

    tmpRow = 1
    'すべてのシートをループする
    For Each ws In Worksheets
        '追加したシートにシート名の一覧を作る
        ActiveSheet.Cells(tmpRow, 1).Value = ws.Name
        tmpRow = tmpRow + 1
    Next ws
    
    '1行目(目次用のシート自身へのハイパーリンク)を削除する
    Rows(1).Delete
    
End Sub

このコードの肝は、まず

Sheets.Add Before:=Worksheets(1)

でブックの先頭に新しいシートを追加します。

このシートに次のコードで目次（シート名の一覧）を作成いていきます。

ActiveSheet.Cells(tmpRow, 1).Value = ws.Name

最後に、目次用のシート自身へのハイパーリンクは有っても意味が無いので、1行目を削除しています。

実行結果はこんな感じです。

Sponsored Link

シート名の一覧表にハイパーリンクを設定する

第二段階として、このシート名の一覧に各シートへのハイパーリンクを設定していきます。

'------------------------------
'シート名の一覧をつくり、各シートへのハイパーリンクを設定する
'------------------------------
Sub ListUpSheetNameWithHyperLink()
    Dim ws As Worksheet
    Dim tmpRow As Long
    'ハイパーリンクで移動したシートにて、アクティブにするセル
    Dim tmpCell As String
    
    'ブックの先頭にシートを追加する
    Sheets.Add Before:=Worksheets(1)
    
    tmpRow = 1
    'すべてのシートをループする
    For Each ws In Worksheets
        'シート名に半角括弧()があると正しく参照できないので、シングルクォートで括る
        tmpCell = "'" & ws.Name & "'" & "!" & "A1"
        With ActiveSheet
            .Cells(tmpRow, 1).Value = ws.Name
            .Cells(tmpRow, 1).Hyperlinks.Delete
            .Hyperlinks.Add Anchor:=Cells(tmpRow, "A"), Address:="", SubAddress:=tmpCell
        End With
        tmpRow = tmpRow + 1
    Next ws
    
    '1行目(目次用のシート自身へのハイパーリンク)を削除する
    Rows(1).Delete

End Sub

ハイパーリンクは下記のコードで作成＆設定しています。

tmpCell = “‘” & ws.Name & “‘” & “!” & “A1”

また、シート名に半角括弧()があると正しく参照できないので、シングルクォートで括っています。

Hyperlinks.Add Anchor:=Cells(tmpRow, “A”), Address:=””, SubAddress:=tmpCell

Hyperlinks.Addメソッドに指定している引数の意味は以下のとおり。

Anchor	ハイパーリンクを設定する場所をセルのRangeオブジェクトの形式で指定します。
Address	URLやファイルパス、メールアドレスなどを設定する場合に使用する引数。このマクロではSubAddressの方を使用するので、空文字列(””)を設定します。
SubAddress	ハイパーリンクで飛んだ先のセルを指定します。

Excelの画面では、以下の設定をしているのと同じ意味になります。