Sub Ventiler()
Dim chemin$, Source, n As Integer, i As Integer, flag As Boolean, model As Workbook, P As Range
chemin = ThisWorkbook.Path & "\" 'dossier à adapter éventuellement
If Dir(chemin & "Ventilation\", vbDirectory) = "" Then MkDir chemin & "Ventilation\" 'crée le sous-dossier
Source = Array("Source_SF.xlsx", "Source_EJ.xlsx", "Source_Chrono.xlsx", "Source_DP.xlsx") 'à adapter
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For n = 0 To UBound(Source)
Workbooks.Open chemin & Source(n) 'ouverture des fichiers sources
Next n
For i = 1001 To 1012
flag = False
Set model = Workbooks.Open(chemin & "Modele.xlsx")
For n = 0 To UBound(Source)
Set P = Workbooks(Source(n)).Sheets(1).Columns(2)
If Application.CountIf(P, i) Then
flag = True
P.Replace i, "#N/A", xlWhole
P.SpecialCells(xlCellTypeConstants, 16).EntireRow.Copy model.Sheets(n + 1).Range("A4")
P.Replace "#N/A", i
model.Sheets(n + 1).Columns(2).Replace "#N/A", i
End If
Next n
If flag Then model.SaveAs chemin & "Ventilation\" & i & ".xlsx": Workbooks(i & ".xlsx").Close
Next i
For n = 0 To UBound(Source)
Workbooks(Source(n)).Close False 'fermeture des fichiers sources
Next n
On Error Resume Next
model.Close
MsgBox "Ventilation effectuée dans le dossier '" & chemin & "Ventilation'"
End Sub