Option Explicit
Dim nbSheets As Long
Private Sub Workbook_Open()
nbSheets = Sheets.Count
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sheets.Count <> nbSheets Then MAJtabelle: nbSheets = Sheets.Count
End Sub
' module copier
Sub MAJtabelle()
Dim lig As Long, i As Long
lig = 3
With Worksheets("Tabelle")
.Range(.[B1:C1], .[B1].End(xlDown)).Offset(2).ClearContents
'Afficher la liste des noms (codename) de toutes les feuilles
For i = 1 To Sheets.Count
.Cells(lig, 2).Value = Sheets(i).CodeName
.Cells(lig, 3).Value = Sheets(i).Name
lig = lig + 1
Next i
End With
End Sub