Private Sub Worksheet_Activate()
MAJ
End Sub
Sub MAJ()
Dim lig&, w As Worksheet, h&, test As Boolean, col%
Application.ScreenUpdating = False
Application.Calculate 'recalcule toutes les feuilles
Application.Calculation = xlCalculationManual 'évite le recalcul
Rows("2:" & Rows.Count).Clear 'RAZ
lig = 2
For Each w In Worksheets
If w.Name <> "recap" And w.[A1] = "NOM" Then
h = w.Range("A" & Rows.Count).End(xlUp).Row - 1
If h Then
test = w.[D1] Like "POSTE*" 'feuille MIRAMAS
col = IIf(test, 12, 11)
w.[A2].Resize(h, col).Copy Cells(lig, 1) 'pour les formats
Cells(lig, 1).Resize(h, col) = w.[A2].Resize(h, col).Value
If test Then Cells(lig, "D").Resize(h).Delete xlToLeft
lig = lig + h
End If
End If
Next
[A:K].Resize(lig).Sort [A1], Header:=xlYes 'tri sur les noms
[A:K].Resize(lig - 1).Borders.Weight = xlThin 'bordures
Columns("A:K").AutoFit 'ajustement de la largeur des colonnes
Application.Calculation = xlCalculationAutomatic
End Sub