Bonsoir Asmodehus,
Le code est à placer dans un module standard du classeur "Faune_Combo.xlsm" soit le classeur cible
Dans quelles feuilles du classeur cible, faut-il ventiler les données peux-tu nous le préciser
Option Explicit
Sub test()
Dim a, dico As Object, i As Long, ws As Worksheet
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
Application.ScreenUpdating = False
With Workbooks.Open(ThisWorkbook.Path & "\Faune_Habitats_Statuts.xls") 'source
For Each ws In .Worksheets([{"Invertébrés","Vertébrés"}])
a = ws.Cells(1).CurrentRegion.Value
For i = 2 To UBound(a, 1)
dico(a(i, 2)) = VBA.Array(a(i, 3), a(i, 4))
Next
Next
.Close False
End With
With ThisWorkbook 'cible
For Each ws In .Worksheets([{"Faune","Flore"}]) '<---- ventilation ?
With ws.Cells(1).CurrentRegion
.Offset(1).Columns("c:d").ClearContents
For i = 2 To .Rows.Count
If dico.exists(.Cells(i, 2).Value) Then
.Cells(i, 3).Resize(, 2).Value = dico(.Cells(i, 2).Value)
End If
Next
End With
Next
End With
Application.ScreenUpdating = True
Set dico = Nothing
End Sub
klin89