機能

「目次」シートを一番左に移動する。
既存の目次をクリアする。
ブック内のシート名を取得し、「目次」シートに一覧化する。
各シートへのリンク（HYPERLINK関数）をシート名分コピーする。
「表示状態」列に表示・非表示を記載（非表示の場合は行単位でグレーアウト）（2022/5/8追加）
オートフィルタ設定（2022/5/8追加）

シート建付け

シート名
目次
構成
※ 目次の開始行、開始列は定数で指定。

ソースコード

Module1

'＜ 共通設定 ＞
'シート名
Public Const SHEET_NAME_INDEX = "目次"


'***** 目次更新 *****
Sub UpdateIndex()
    
    '行数
    Const INDEX_START_ROW = 2
    '列数
    Const NO_COL = 1
    Const SHEET_NAME_COL = 2
    Const LINK_COL = 3
    Const VISIBLE_STATUS_COL = 4
    '関数
    Const LINK_FORMURA = _
        "=IF(B2<>"""",HYPERLINK(""#'"" & B2 & ""'!A1"", ""→ "" & B2), """")"
    '文言
    Const STATUS_VISIBLE = "表示"
    Const STATUS_INVISIBLE = "非表示"
    '終了メッセージ
    Const END_MESSAGE = "更新完了"
    
    'No 初期化
    Dim cntNo As Long
    cntNo = 1
    '編集行 初期化
    Dim workRow As Long
    workRow = INDEX_START_ROW
    
    
    '処理前アクション
    StartAction SHEET_NAME_INDEX
    
    
    'シート表示状況 取得
    Dim visibleFlagDic As Object
    Set visibleFlagDic = SetVisibleFlagDic


    '全シート 表示
    AllSheetsVisible
    
    
    '「目次」を先頭に移動
    Sheets(SHEET_NAME_INDEX).Move before:=Sheets(1)
    
    
    'シート数 取得
    Dim ThisBookName As String
    ThisBookName = ThisWorkbook.Name
    Dim sheetCount As Long
    sheetCount = GetSheetCount(ThisBookName)
    
    
    '既存データ 削除
    Dim maxRow As Long
    maxRow = GetMaxRow(SHEET_NAME_INDEX, NO_COL)
    CellClear _
        SHEET_NAME_INDEX, _
        INDEX_START_ROW, _
        maxRow, _
        NO_COL, _
        VISIBLE_STATUS_COL


    '2行目 初期化
    With Cells(INDEX_START_ROW, NO_COL)
        'No
        .Value = cntNo
        'シート名
        .Offset(0, 1).Value = SHEET_NAME_INDEX
        'リンク
        .Offset(0, 2).Formula = LINK_FORMURA
        '表示状態
        If visibleFlagDic.Item(SHEET_NAME_INDEX) = True Then
            .Offset(0, 3).Value = STATUS_VISIBLE
        Else
            .Offset(0, 3).Value = STATUS_INVISIBLE
        End If
        .Offset(0, 3).HorizontalAlignment = xlCenter
    End With
    
    
    'シート名 記入
    Dim i As Long
    
    For i = 1 To sheetCount
        '3行目以降に対する処理
        If workRow > INDEX_START_ROW Then
            'No挿入
            Cells(workRow, NO_COL).Value = cntNo
            'シート名
            Cells(workRow, SHEET_NAME_COL).Value = Sheets(i).Name
            'リンク表示関数
            FormulaCopyDown workRow, LINK_COL
            '表示状態
            With Cells(workRow, VISIBLE_STATUS_COL)
                If visibleFlagDic.Item(Sheets(i).Name) = True Then
                    .Value = STATUS_VISIBLE
                Else
                    .Value = STATUS_INVISIBLE
                    Range(.Offset(0, -3), .Offset(0, 0)).Interior.ColorIndex = WEAK_GRAY
                End If
                .HorizontalAlignment = xlCenter
            End With
        End If

        'No 更新
        cntNo = cntNo + 1
        '次の行へ
        workRow = workRow + 1
    
    Next i
    
    
    'オートフィルタ
    Selection.CurrentRegion.AutoFilter
         
         
    '（非表示だったシートのみ）再非表示
    Dim tmpVisFlag As Boolean
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        tmpVisFlag = visibleFlagDic.Item(ws.Name)
        
        If tmpVisFlag = False Then
            ws.Visible = tmpVisFlag
        End If
    
    Next
    
         
    '列幅自動調整
    ColAutoFit NO_COL, LINK_COL
    
    
    '表示倍率：100％
    ZoomSet 100
    
    
    '処理後アクション
    EndAction SHEET_NAME_INDEX, END_MESSAGE


End Sub

Module2

Option Explicit

'■ ■ ■　よく使う処理　■ ■ ■

'***** 処理前アクション *****
'機能：処理開始前にExcelの描画を止める（任意）
Sub StartAction(ByVal sheetName As String, Optional ByVal ScreenUpdateFlag As Boolean = True)

    ThisWorkbook.Activate
    
    '描画停止
    If ScreenUpdateFlag = False Then
        Application.ScreenUpdating = ScreenUpdateFlag
    End If
    
    '対象シート 有効化
    Worksheets(sheetName).Activate

End Sub


'***** 処理後アクション *****
'機能：① Excelの描画を再開させる。
'　　：② [Ctrl + HOME] を押下した位置にカーソルを移動させる。
'　　：③ 終了メッセージを表示させる。
Sub EndAction(ByVal sheetName As String, ByVal endMessage As String)

    ThisWorkbook.Activate
    
    '描画再開
    Application.ScreenUpdating = True
    
    '対象シート 有効化
    Sheets(sheetName).Activate
    
    'Ctrl + HOMEの位置にカーソル移動
    With ActiveWindow
        ActiveWindow.ActiveSheet.Cells(.SplitRow + 1, .SplitColumn + 1).Activate
    End With
    
    '終了メッセージ 表示
    MsgBox endMessage

End Sub


'***** 表示倍率 変更 *****
'機能: 指定した表示倍率に変更する｡
Sub ZoomSet(ByVal zoomPercentage As Long)

    '表示倍率 変更
    ActiveWindow.ZOOM = zoomPercentage

End Sub


'***** 最大行 取得 *****
'機能：最大行を取得する。
Function GetMaxRow(ByVal targetSheetName As String, ByVal targetCol As Long) As Long

    GetMaxRow = Sheets(targetSheetName).Cells(Rows.Count, targetCol).End(xlUp).Row

End Function


'***** シート数 取得 *****
'機能：対象ブックのシート数を取得する。
Function GetSheetCount(ByVal targetBookName As String) As Long

    GetSheetCount = Workbooks(targetBookName).Sheets.Count

End Function


'***** 数式コピー（Ctrl + D） *****
'機能：一行上の数式をコピーする。
Sub FormulaCopyDown(ByVal targetRow As Long, ByVal targetCol As Long)

    Cells(targetRow - 1, targetCol).Copy
    Cells(targetRow, targetCol).PasteSpecial Paste:=xlPasteFormulas

End Sub


'***** 列幅 自動調整 *****
'機能：対象列（複数でも可）の幅を自動調整する。
Sub ColAutoFit(ByVal startCol As Long, ByVal endCol As Long)

    Range(Columns(startCol), Columns(endCol)).AutoFit

End Sub


'***** シート表示状態 取得 *****
'機能：全シートの表示 / 非表示を連想配列に記録して返す。
Function SetVisibleFlagDic() As Object
    
    Dim tmpDic As Object
    Set tmpDic = CreateObject("Scripting.Dictionary")
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        tmpDic.Add ws.Name, ws.Visible
    Next
    
    Set SetVisibleFlagDic = tmpDic

End Function


'***** 全シート表示 *****
'機能：全シートの表示させる。
Sub AllSheetsVisible(Optional ByVal bookName As String = "")
    
    Dim ws As Worksheet
    
    If bookName <> "" Then
        Workbooks(bookName).Activate
    Else
        ThisWorkbook.Activate
    End If
    
    For Each ws In Worksheets
        ws.Visible = True
    Next
    
End Sub


'***** 対象範囲 クリア *****
'機能：対象範囲のデータや書式を削除
Sub CellClear(ByVal sheetName As String, ByVal startRow As Long, ByVal endRow As Long, ByVal startCol As Long, ByVal endCol As Long)

    Sheets(sheetName).Range( _
        Cells(startRow, startCol), Cells(endRow, endCol) _
    ).Clear

End Sub

ソースコードの全量はこちら