VBAでシートの詳細情報を一覧出力する

シートの一覧の用途

Excelのシートの数が増えてくるとシートの一覧が必要になることがあります。

一覧を取得する際の表現方法や出力内容にはいくつか考えられます。

シート一覧のシートを新規作成する。
非表示のシートは一覧に出力しない。
各シートに番号を付ける。（A列）
各シートの名前を列挙する。（B列）
目次シートを作って各シートへのリンクを張る。（C列）
各シート名のフルパスを出力する。（D列）
各シートの指定したセルの値を取得する。（E列）
各シート名で並べ替えをする。

これらを実現するマクロです。

いらない部分は削除してください。

ソースコード

Sub MakeSheetList()
    Dim sht As Worksheet    '// 各シート
    Dim i                   '// ループインデックス
    Dim bookPath            '// ワークブックのパス
    Dim sAddName            '// 追加するシート名
    
    '// 一番左にシートを追加
    Worksheets.Add Before:=Worksheets(1)

    '// シート名の重複が無いように現在日時でシート名を作成（ここは適当な名前を付けてください）
    sAddName = Format(Now, "yyyymmdd-hhmmss")
    ActiveSheet.Name = sAddName

    '// 追加したシートのA1セルを選択（この処理はしなくてもよいですが説明の便宜上入れてます）
    ActiveSheet.Range("A1").Select
    
    i = 0
    '// 現在のブックのファイルパスを取得
    bookPath = ActiveWorkbook.FullName
    
    '// 全シートをループ
    For Each sht In Sheets
        '// シートが表示されている場合
        If (sht.Visible <> xlSheetHidden) And (sht.Visible <> xlSheetVeryHidden) Then
            '// シートの番号をA列に出力
            ActiveCell.Offset(i, 0).Value = "No." & CStr(i + 1)
            
            '// シート名をB列に出力
            ActiveCell.Offset(i, 1).NumberFormatLocal = "@"
            ActiveCell.Offset(i, 1).Value = sht.Name
            
            '// シートへのリンクをC列に出力
            ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(i, 2), Address:="", SubAddress:="'" & sht.Name & "'!A1", TextToDisplay:=sht.Name
            '// リンク用の下線表示
            ActiveCell.Offset(i, 2).Font.Underline = xlUnderlineStyleSingle
            '// リンク用に青文字表示
            ActiveCell.Offset(i, 2).Font.ColorIndex = 5
            
            '// フルパスをD列に出力
            ActiveCell.Offset(i, 3).Value = bookPath & "#'" & sht.Name & "'!A1"
            
            '// A1セルの値をE列に出力
            ActiveCell.Offset(i, 4).Value = sht.Range("A1").Value
            
            i = i + 1
        End If
    Next
    
    '// 出力したA列からD列までの幅を自動調整
    Range("A:D").Columns.AutoFit
    
    '// シート名で並べ替え
    Columns("B:B").Select
    ActiveWorkbook.Worksheets(sAddName).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(sAddName).Sort.SortFields.Add Key:=Range( _
        "B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets(sAddName).Sort
        .SetRange Range("A:D")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub MakeSheetList()

Dim sht As Worksheet '// 各シート

Dim i '// ループインデックス

Dim bookPath '// ワークブックのパス

Dim sAddName '// 追加するシート名

'// 一番左にシートを追加

Worksheets.Add Before:=Worksheets(1)

'// シート名の重複が無いように現在日時でシート名を作成（ここは適当な名前を付けてください）

sAddName = Format(Now, "yyyymmdd-hhmmss")

ActiveSheet.Name = sAddName

'// 追加したシートのA1セルを選択（この処理はしなくてもよいですが説明の便宜上入れてます）

ActiveSheet.Range("A1").Select

i = 0

'// 現在のブックのファイルパスを取得

bookPath = ActiveWorkbook.FullName

'// 全シートをループ

For Each sht In Sheets

'// シートが表示されている場合

If (sht.Visible <> xlSheetHidden) And (sht.Visible <> xlSheetVeryHidden) Then

'// シートの番号をA列に出力

ActiveCell.Offset(i, 0).Value = "No." & CStr(i + 1)

'// シート名をB列に出力

ActiveCell.Offset(i, 1).NumberFormatLocal = "@"

ActiveCell.Offset(i, 1).Value = sht.Name

'// シートへのリンクをC列に出力

ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(i, 2), Address:="", SubAddress:="'" & sht.Name & "'!A1", TextToDisplay:=sht.Name

'// リンク用の下線表示

ActiveCell.Offset(i, 2).Font.Underline = xlUnderlineStyleSingle

'// リンク用に青文字表示

ActiveCell.Offset(i, 2).Font.ColorIndex = 5

'// フルパスをD列に出力

ActiveCell.Offset(i, 3).Value = bookPath & "#'" & sht.Name & "'!A1"

'// A1セルの値をE列に出力

ActiveCell.Offset(i, 4).Value = sht.Range("A1").Value

i = i + 1

End If

'// 出力したA列からD列までの幅を自動調整

Range("A:D").Columns.AutoFit

'// シート名で並べ替え

Columns("B:B").Select

ActiveWorkbook.Worksheets(sAddName).Sort.SortFields.Clear

ActiveWorkbook.Worksheets(sAddName).Sort.SortFields.Add Key:=Range( _

"B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

xlSortNormal

With ActiveWorkbook.Worksheets(sAddName).Sort

.SetRange Range("A:D")

.Header = xlNo

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

ソースコードの説明

コードのコメントにほとんど書いているのでそれ以外の補足です。

シート名の設定

このソースでは強制的に一番左にシートを追加しています。

シート名には現在日時を入れています。

10行目の=から右側は適宜シート名を入れてください。

例えば「シート一覧」という名前のシートに出力したい場合は、２通りのやり方があります。

シートを事前に作っておく方法

１つは「シート一覧」シートを作っておく方法です。

7行目から12行目を以下に書き換えます。

    '// 一番左にシートを追加
    Worksheets.Add Before:=Worksheets(1)

    '// シート名の重複が無いように現在日時でシート名を作成（ここは適当な名前を付けてください）
    sAddName = Format(Now, "yyyymmdd-hhmmss")
    ActiveSheet.Name = sAddName

'// 一番左にシートを追加

Worksheets.Add Before:=Worksheets(1)

'// シート名の重複が無いように現在日時でシート名を作成（ここは適当な名前を付けてください）

sAddName = Format(Now, "yyyymmdd-hhmmss")

ActiveSheet.Name = sAddName

↓

    sAddName = "シート一覧"
    Sheets("シート一覧").Select

7 8	sAddName = "シート一覧" Sheets("シート一覧").Select

シートを作り直す方法

もう１つは「シート一覧」シートを作り直す方法です。

この場合も7行目から12行目を以下に書き換えます。

    '// 一番左にシートを追加
    Worksheets.Add Before:=Worksheets(1)

    '// シート名の重複が無いように現在日時でシート名を作成（ここは適当な名前を付けてください）
    sAddName = Format(Now, "yyyymmdd-hhmmss")
    ActiveSheet.Name = sAddName

'// 一番左にシートを追加

Worksheets.Add Before:=Worksheets(1)

'// シート名の重複が無いように現在日時でシート名を作成（ここは適当な名前を付けてください）

sAddName = Format(Now, "yyyymmdd-hhmmss")

ActiveSheet.Name = sAddName

↓

    sAddName = "シート一覧"
    '// 「シート一覧」シートを削除
    Application.DisplayAlerts = False
    Sheets(sAddName).Delete
    Application.DisplayAlerts = True
    '// 一番左にシートを追加
    Worksheets.Add Before:=Worksheets(1)
    '// シート名を「シート一覧」に設定
    ActiveSheet.Name = sAddName

sAddName = "シート一覧"

'// 「シート一覧」シートを削除

Application.DisplayAlerts = False

Sheets(sAddName).Delete

Application.DisplayAlerts = True

'// 一番左にシートを追加

Worksheets.Add Before:=Worksheets(1)

'// シート名を「シート一覧」に設定

ActiveSheet.Name = sAddName

Application.DisplayAlerts = Falseは、シート削除時の警告メッセージの表示をさせないための処理です。

Sheetsコレクション

上のソースコードの22行目で全シートの意味であるSheetsコレクションを利用しています。

Sheetsコレクションには全てのシートの情報が設定されており、ブックの一番左のシートから一番右のシートに向かって登録されています。

そのため、For Eachループで各ループで設定されるsht変数には一番左のシートから順に設定されていきます。

シート名で並べ替え

簡単にではありますが、45行目以降にシート名での並べ替え処理を入れています。

必要ないのであれば削除してください。

非表示シートがある場合

なんらかの理由でシートを非表示にしているブックがあります。

そのため、非表示のシートは一覧には出力したくない、という要件も出てくると思います。

24行目のif文は非表示のシートは除外する条件文です。

シートの表示形式には３通りあります。

表示されている状態（xlSheetVisible）
Excelの操作で再表示可能な非表示の状態（xlSheetHidden）
Excelの操作で再表示できない非表示の状態（xlSheetVeryHidden）

24行目のif文に２つの条件があるのはそのためです。

SheetsコレクションとWorksheetsコレクションの違い

上のソースコードではSheetsコレクションを使っています。

でもWorksheetsコレクションに書き換えてもおそらく同じ期待結果になると思います。

違いはWorksheetsコレクションはワークシートのみが対象となり、Sheetsコレクションはワークシートだけでなくシート挿入時の画面に表示されるグラフシートやExcel 4.0 マクロシートやMS Excel 5.0 ダイアログシートも対象になります。

ただ、グラフシートはあるかもしれませんがExcel 4.0 マクロシートやMS Excel 5.0 ダイアログシートを使うことはまずないでしょうから、SheetsもWorksheetsも気にしなくて大丈夫かなあ、と思います。

ワークフシート以外を使うことがない私は常にSheetsと書いてます。

使用例

実際に私がこのマクロを繰り返し使った例です。

見出しがないなあ、と動かしてみて思ったのですが、そこは適宜作ってみてください。

15行目のA1をA2にすれば一覧は2行目以降に出力されますのであとは1行目に見出しを固定で出力して対応してみてください。

シートの一覧の用途

ソースコード

ソースコードの説明

シート名の設定

シートを事前に作っておく方法

シートを作り直す方法

Sheetsコレクション

シート名で並べ替え

非表示シートがある場合

SheetsコレクションとWorksheetsコレクションの違い

使用例

関連記事