Sub Consolider()
Dim lig As Long, w As Worksheet, h As Long, col As Byte
Feuil7.Activate 'CodeName de "suivi Congés"
Application.ScreenUpdating = False 'fige l'écran
Application.DisplayAlerts = False 'évite les invites éventuelles (liaisons)
Rows("5:" & Rows.Count).Delete 'vidage
lig = 5 '1ère ligne à remplir
For Each w In Sheets(Array("Prof ", "Modèle", "Admin", "Etudiant", "Personnel ss Facture")) 'espace après Prof...
h = w.Cells(Rows.Count, "C").End(xlUp).Row - 4 'normalement colonne A mais ne convient pas ici...
If h > 0 Then
Cells(lig, "D").Resize(h) = w.Name 'Emploi
w.[A5].Resize(h, 3).Copy Cells(lig, 1) 'pour les formats
Cells(lig, 1).Resize(h, 3) = w.[A5].Resize(h, 3).Value 'valeurs colonnes A:C
col = w.[4:4].Find("=*", LookIn:=xlFormulas).Column
w.Cells(5, col).Resize(h, 32).Copy Cells(lig, "E") 'colonnes des dates
col = w.[4:4].Find("commentaires*").Column
w.Cells(5, col).Resize(h).Copy Cells(lig, "AK") 'pour les formats
Cells(lig, "AK").Resize(h) = w.Cells(5, col).Resize(h).Value 'valeurs colonne AK
lig = lig + h
End If
Next
If lig = 5 Then Exit Sub 'si aucun nom
With [5:5].Resize(lig - 5)
.Columns(3).AutoFill .Columns(3).Resize(, 2), xlFillFormats 'format colonne D
.Sort [A5], Header:=xlNo 'tri sur colonne A
'---épuration---
.Columns(1).Replace " ", "", LookAt:=xlWhole
On Error Resume Next
.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub