AW: Vba verschieben in tabellenblatter
13.09.2017 13:10:04
Bernd
Servus,
z.B. so:
Sub sortieren()
Dim wsWerkzeug As Worksheet
Dim wsTextil As Worksheet
Dim wsGetränke As Worksheet
Dim ws As Worksheet
Dim intLZ As Integer
Dim i As Integer
Set ws = ThisWorkbook.Sheets("Tabelle1")
Set wsWerkzeug = ThisWorkbook.Sheets("Werkzeug")
Set wsTextil = ThisWorkbook.Sheets("Textil")
Set wsGetränke = ThisWorkbook.Sheets("Getränke")
intLZ = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = intLZ To 2 Step -1
Select Case ws.Cells(i, 11).Value
Case Is = "Schrauben"
ws.Rows(i).Copy Destination:=wsWerkzeug.Cells((wsWerkzeug.Cells(Rows.Count, 1).End(xlUp). _
Row + 1), 1)
ws.Rows(i).EntireRow.Delete
Case Is = "Wolle"
ws.Rows(i).Copy Destination:=wsTextil.Cells((wsTextil.Cells(Rows.Count, 1).End(xlUp).Row + _
1), 1)
ws.Rows(i).EntireRow.Delete
Case Is = "Cola"
ws.Rows(i).Copy Destination:=wsGetränke.Cells((wsGetränke.Cells(Rows.Count, 1).End(xlUp). _
Row + 1), 1)
ws.Rows(i).EntireRow.Delete
Case Else
MsgBox "Benennung nicht bekannt"
End Select
Next i
Set ws = Nothing
Set wsWerkzeug = Nothing
Set wsTextil = Nothing
Set wsGetränke = Nothing
End Sub
Grüße, Bernd