1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| Private Sub Worksheet_change(ByVal Target As Range)
Dim Ligne As Long, Plage As Range, C As Range
Dim Tabl As Variant
If Target.Address = "$D$1" And Target.Value <> "" Then
Application.EnableEvents = False
With Sheets("Suivi livrables")
Tabl = .Range("A7", .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 121)
End With
With Sheets("Foams FNR export")
Ligne = .Cells(.Rows.Count, 1).End(xlUp).Row
If Ligne = 4 Then Ligne = 6
For i = 1 To UBound(Tabl, 1)
If Tabl(i, 17) = .[D1] Or Tabl(i, 19) = .[D1] Or Tabl(i, 21) = .[D1] Or Tabl(i, 23) = .[D1] Or Tabl(i, 25) = .[D1] Or Tabl(i, 28) = .[D1] Or Tabl(i, 31) = .[D1] Then
Ligne = Ligne + 1
If Not IsError(Tabl(i, 5)) Then .Cells(Ligne, 2) = Tabl(i, 5)
If Not IsError(Tabl(i, 6)) Then .Cells(Ligne, 3) = Tabl(i, 6)
If Not IsError(Tabl(i, 7)) Then .Cells(Ligne, 4) = Tabl(i, 7)
If Not IsError(Tabl(i, 8)) Then .Cells(Ligne, 5) = Tabl(i, 8)
If Not IsError(Tabl(i, 9)) Then .Cells(Ligne, 6) = Tabl(i, 9)
'etc.
' Tabl(i, 6) = .Cells(Ligne, 3)
' Tabl(i, 7) = .Cells(Ligne, 4)
' Tabl(i, 8) = .Cells(Ligne, 5)
' Tabl(i, 9) = .Cells(Ligne, 6)
' Tabl(i, 10) = .Cells(Ligne, 7)
' Tabl(i, 11) = .Cells(Ligne, 8)
' Tabl(i, 77) = .Cells(Ligne, 9)
' Tabl(i, 78) = .Cells(Ligne, 10)
' Tabl(i, 79) = .Cells(Ligne, 11)
' Tabl(i, 80) = .Cells(Ligne, 12)
' Tabl(i, 81) = .Cells(Ligne, 13)
End If
Next i
End With
Application.EnableEvents = True
End If
End Sub |
Partager