Private Sub Worksheet_Activate()
Dim P As Range, t, i&, j%, k As Variant
With Sheets("B") 'à adapter
Set P = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
End With
t = Range("A3:E" & Range("A" & Rows.Count).End(xlUp).Row + 1)
For i = 1 To UBound(t) Step 2
For j = 2 To 5 'RAZ
t(i, j) = "": t(i + 1, j) = ""
Next
k = Application.Match(t(i, 1), P, 0)
If IsNumeric(k) Then
t(i, 2) = P(k, 2) & " " & P(k, 3)
t(i, 3) = P(k, 5)
t(i, 4) = P(k, 7)
t(i, 5) = P(k, 9)
t(i + 1, 2) = P(k, 4)
t(i + 1, 3) = P(k, 6)
t(i + 1, 4) = P(k, 8)
t(i + 1, 5) = P(k, 10)
End If
Next
[A3].Resize(UBound(t), 5) = t
Range("B" & UBound(t) + 3 & ":E" & Rows.Count).ClearContents
End Sub