たまに、シートが多いファイルを利用する時とかシートで分けて作成して、目次を作る必要がある場合もあります。
その場合、量が多いと、手動でするのはつらいので、作成して見ました。
SheetListMake
Sub SheetListMake()
Dim list_sht As Worksheet
Dim sht As Worksheet
Dim i As Integer
Dim t1 As Range
Dim t2 As Range
Dim condition As Range
Dim d1 As Range
Dim d2 As Range
Dim d3 As Range
'リストを書き出すシート名
Set list_sht = Worksheets("list_sheet_name")
i = 2
For Each sht In Worksheets
Set condition = sht.Range("A1")
If condition = "targetText" Then
Set t1 = sht.Range("C1")
Set t2 = sht.Range("C2")
Set d1 = list_sht.Range("B" & i)
Set d2 = list_sht.Range("F" & i)
Set d3 = list_sht.Range("J" & i)
d1.Value = "=Hyperlink(""#" & sht.Name & "!A1"", """ & t1.Text & """)"
d2.Value = t2.Text
Debug.Print sht.Name & " | " & t1.Interior.ColorIndex
If t1.Interior.ColorIndex = 16 Then
d3.Value = "N"
Else
d3.Value = "Y"
End If
i = i + 1
End If
Next sht
End Sub