Sub Macro1()
Application.ScreenUpdating = False
pfile = ActiveWorkbook.Path & "\archive\" 'indiquer ici le chemin du répertoire
nfile = Dir(pfile)
i = 2
Do Until nfile = ""
texte = "'" & pfile & "[" & nfile & "]FORMULAIRE'!"
Cells(i, 8).FormulaArray = "=COUNTA(" & texte & "$C$18:C118)"
For j = 1 To Cells(i, 8)
Cells(i, 1) = Year(Date)
Cells(i, 2) = Int((Month(Date) + 2) / 3)
Cells(i, 3) = "=" & texte & "$C$4"
Cells(i, 4) = "=IF(" & texte & "$C$5=""""," & texte & "$C$6," & texte & "$C$5)"
Cells(i, 5) = "=" & texte & "$B$" & j + 17
Cells(i, 6) = "=" & texte & "$C$" & j + 17
Cells(i, 7) = "=" & texte & "$E$" & j + 17
i = i + 1
Next
nfile = Dir()
Loop
Columns(8).Cells.Clear
With Range("A2:G" & Range("A65000").End(xlUp).Row)
.Value = .Value
End With
End Sub