Private Sub Worksheet_Activate()
Range("A3:H1000").ClearContents
For Each cell In Sheets("d. Devis").Range("A14:A" & Sheets("d. Devis").[a65000].End(xlUp).Row)
If cell <> "" Then
r = [b65000].End(xlUp).Row + 2
Range("a" & r) = cell.Value
Range("h" & r) = cell.Offset(0, 4) 'ou range("e" & cell.row)
For J = Cells(r, 2).Row + 1 To Cells(r, 2).End(xlDown).Row
Cells(J, 8).Formula = "=" & Range("h" & r).Address & "*E" & J
Next J
End If
Next cell
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim k&, i&, J&, m
[a1].Select
On Error GoTo fin
k = Application.Match(Target, Feuil2.Columns(1), 0)
m = Feuil2.Cells(k, 1).CurrentRegion.Value
Target.Offset(0, 1).Resize(UBound(m, 1), UBound(m, 2)) = m
fin:
End Sub