Creer un sommaire des noms de feuille et recap données

Sub Liste_Affaires()
Dim t(), w As Worksheet, n&
ReDim t(1 To Worksheets.Count, 1 To 7)
For Each w In Worksheets
  If Val(w.Name) Then
    n = n + 1
    t(n, 1) = "=HYPERLINK(""#'" & w.Name & "'!A1"",""" & w.Name & """)"
    t(n, 2) = "???" 'que fait-on ?
    t(n, 3) = w.[A2]
    t(n, 4) = w.[C3]
    t(n, 5) = w.[C4]
    t(n, 6) = w.[C7]
    t(n, 7) = w.[C6]
  End If
Next
With Sheets("Sommaire")
  .[A6].Resize(UBound(t), 7) = t
  .Rows(UBound(t) + 6 & ":" & Rows.Count).Delete
End With
End Sub

Sub Liste_Affaires()
Dim t(), w As Worksheet, n&
ReDim t(1 To Worksheets.Count, 1 To 7)
On Error Resume Next 'si les recherches n'aboutissent pas
For Each w In Worksheets
  If Val(w.Name) Then
    n = n + 1
    t(n, 1) = "=HYPERLINK(""#'" & w.Name & "'!A1"",""" & w.Name & """)"
    t(n, 2) = "???" 'que fait-on ?
    t(n, 3) = w.[A2]
    t(n, 4) = w.[C3]
    t(n, 5) = w.[C4]
    t(n, 6) = w.Cells(Application.Match("*preventif", w.[A:A], 0), 3)
    t(n, 7) = w.Cells(Application.Match("*correctif", w.[A:A], 0), 3)
  End If
Next
With Sheets("Sommaire")
  .[A6].Resize(UBound(t), 7) = t
  .Rows(UBound(t) + 6 & ":" & Rows.Count).Delete
End With
End Sub