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
| Private Sub Worksheet_Change(ByVal Target As Range)
Dim x, y As Integer
If (Target.Address = "$H$5") And Not IsEmpty(Target.Value) Then
With Application.ThisWorkbook
.Sheets("Liste1").Range("A9:I65000").ClearContents
x = 3
y = 8
While (Sheets("Base A").Range("H" & x) <> "")
If Sheets("Base A").Range("H" & x).Value = Sheets("Liste1").Range("H5").Value Then
With Sheets("Liste1")
.Range(.Cells(y, "A"), .Cells(y, "g")).Value = Sheets("Base A").Range(Sheets("Base A").Cells(x, "A"), Sheets("Base A").Cells(x, "g")).Value
.Range(.Cells(y, "H"), .Cells(y, "I")).Value = Sheets("Base A").Range(Sheets("Base A").Cells(x, "I"), Sheets("Base A").Cells(x, "J")).Value
End With
y = y + 1
End If
x = x + 1
Wend
End With
z = 8
While (Sheets("Liste1").Range("A" & z) <> "")
z = z + 1
Wend
Sheets("Liste1").Range("A8:I" & z).Sort Key1:=Range("A8"), Order1:=xlAscending, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub |