Private Sub Worksheet_Activate()
Dim Sh As Worksheet, c As Range, feuilles
Dim lig As Long, col As Long
' crea
For Each Sh In Worksheets
If Sh.Name <> "Sommaire" Then
Set c = Columns(1).Find(Sh.Name, , xlValues, xlWhole)
If c Is Nothing Then
lig = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(lig, 1) = Sh.Name
For col = 2 To 20
Cells(lig, col).Formula = Replace(Cells(2, col).Formula, "'" & Cells(2, 1) & "'", "'" & Sh.Name & "'")
Next col
Cells(2, "H").Copy Cells(lig, "H")
Cells(2, "M").Copy Cells(lig, "M")
End If
End If
Next Sh
' supp
feuilles = [A1].Resize(Cells(Rows.Count, 1).End(xlUp).Row)
For lig = UBound(feuilles) To 2 Step -1
If existSheet(feuilles(lig, 1)) = 0 Then Rows(lig).EntireRow.Delete
Next lig
End Sub
Function existSheet(ByVal nomFeuille As String) As Long
On Error Resume Next
existSheet = Sheets(nomFeuille).Index
End Function