Sub Bouton1_QuandClic()
Dim rep As Variant
Dim dossier As Variant
Dim chemin As String, chemin2 As String, fichier As String
Dim k As Byte, i As Byte, x As Byte
x = 1
Dim tableau()
rep = Array('ATEE', 'CPGE')
dossier = Array('air', 'bruit', 'déchets', 'eau', 'emission d'odeur', 'energie', _
'integration paysagere', 'rejet eaux pluviales', 'ressource en eau', 'sol')
For i = 0 To UBound(rep)
chemin = 'c:\\tomato\\' & rep(i) '<=====a adapter
For k = 0 To UBound(dossier)
Sheets.Add.Name = rep(i) & '-' & dossier(k)
chemin2 = chemin & '\\' & dossier(k) & '\\*.xls'
fichier = Dir(chemin2)
While fichier <> ''
ReDim Preserve tableau(1 To x)
tableau(x) = chemin & '\\' & dossier(k) & '\\' & fichier
x = x + 1
fichier = Dir
Wend
Next k
Next i
For i = 1 To UBound(tableau, 1)
Sheets('feuil1').Range('a' & i) = tableau(i)
Next i
End Sub