Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tabl1 As Variant, Tabl2 As Variant, I As Long, J As Long
Dim L1 As Long, L2 As Long, Ligne As Variant
If Target.Address = "$D$2" Then
Application.EnableEvents = False
Range("E2", Cells(Rows.Count, 5).End(xlUp)).Resize(, 9) = ""
Tabl1 = Range("A2", Cells(Rows.Count, 2).End(xlUp))
With Sheets("composants_fonctions")
Tabl2 = .Range("A2", .Cells(.Rows.Count, 2).End(xlUp))
End With
With Sheets("prescriptions_creneaux")
L1 = 1
For I = 1 To UBound(Tabl1, 1)
If Tabl1(I, 1) = Target Then
L1 = L1 + 1
Cells(L1, 5) = Tabl1(I, 2)
L2 = L1 - 1
For J = 1 To UBound(Tabl2, 1)
If Tabl2(J, 2) = Tabl1(I, 2) Then
L2 = L2 + 1
Cells(L2, 6) = Tabl2(J, 1)
'"Ligne" récupère la ligne de la prescription sur la feuille prescriptions_creneaux
Ligne = Application.Match(Target.Value, .[A:A], 0)
'Si la prescription est trouvée, on copie les informations
If IsNumeric(Ligne) And L2 = 2 Then
.Range(.Cells(Ligne, 2), .Cells(Ligne, .Columns.Count).End(xlToLeft)).Copy
Cells(L2, 7).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
End If
Next J
End If
Next I
End With
Application.EnableEvents = True
End If
End Sub