Private Sub Workbook_Activate()
Dim fichier$, F As Worksheet, lig&, w As Worksheet, nf$, dates, tablo, i&
fichier = ThisWorkbook.Path & "\FTE VBA.xlsm" 'à adapter
If Dir(fichier) = "" Then MsgBox "Fichier '" & fichier & "' introuvable !", 48: Exit Sub
Set F = Feuil1 'CodeName
lig = 2 '1ère ligne de destination
Application.ScreenUpdating = False
F.Rows(lig & ":" & F.Rows.Count).ClearContents 'RAZ
With Workbooks.Open(fichier)
For Each w In .Worksheets
nf = w.Name
dates = Application.Transpose(w.Range("M11:X11"))
tablo = w.Range("C11").CurrentRegion.Resize(, 3)
For i = 2 To UBound(tablo)
F.Cells(lig, 1).Resize(12) = tablo(i, 1)
F.Cells(lig, 2).Resize(12) = nf
F.Cells(lig, 3).Resize(12) = tablo(i, 2)
F.Cells(lig, 4).Resize(12) = tablo(i, 3)
F.Cells(lig, 6).Resize(12) = dates
lig = lig + 12
Next i, w
Application.EnableEvents = False 'désactive l'évènement Open
.Close False
Application.EnableEvents = True 'réactive les évènements
End With
End Sub