Sub Consolider()
Dim chemin$, fichier$, ad$(1 To 3, 1 To 2), lig&, w As Worksheet, n As Byte
chemin = ThisWorkbook.Path & "\" 'dossier à adapter
fichier = Dir(chemin & "*.xl*") '1er fichier du dossier
ad(1, 1) = "D14": ad(1, 2) = "E14" 'adresses à adapter
ad(2, 1) = "I11": ad(2, 2) = "J11" 'adresses à adapter
ad(3, 1) = "I16": ad(3, 2) = "J16" 'adresses à adapter
Application.ScreenUpdating = False
With Feuil1 'CodeName, à adapter
.Range("A2:C" & .Rows.Count).ClearContents 'RAZ
lig = 1
While fichier <> ""
If fichier <> ThisWorkbook.Name Then
Workbooks.Open chemin & fichier
For Each w In ActiveWorkbook.Worksheets
lig = lig + 1
For n = 1 To 2
.Cells(lig, 1) = w.Range(ad(1, n))
.Cells(lig, 2) = w.Range(ad(2, n))
.Cells(lig, 3) = w.Range(ad(3, n)).Value
If .Cells(lig, 1) <> "" And .Cells(lig, 2) <> "" And .Cells(lig, 3) <> "" Then Exit For
Next n, w
ActiveWorkbook.Close False
End If
fichier = Dir 'fichier suivant
Wend
.Columns("A:C").AutoFit 'ajustement largeurs
End With
End Sub