Sortieren ws3 makro
01.08.2006 22:10:46
Karel
wie kann ich nach Kopieren nach Tabelle ws3 alle Daten Sortieren nach spalte c
habe selbst probiert mit.
For LN = 2 To 20
ws1.Range(ws1.Cells(LN, 2), ws1.Cells(LN, 20)).Select
Selection.Sort Range.Cells(LN, 3), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
aber noch nicht genugend erfahrung.
Sub Daten_verschieben_10()
Application.ScreenUpdating = False
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("start")
Set ws2 = ThisWorkbook.Worksheets("Zertifikate")
Set ws3 = ThisWorkbook.Worksheets("Wein")
Dim LN, r2, r3
r2 = 2 ' 1. Zeile zum Einfügen von Daten in Tabelle 2
r3 = 29 ' 1. Zeile zum Einfügen von Daten in Tabelle 3
For LN = 29 To 2000
ws3.Range(ws3.Cells(LN, 2), ws3.Cells(LN, 20)).ClearContents
ws3.Range(ws3.Cells(LN, 22), ws3.Cells(LN, 22)).ClearContents
Next LN
For LN = 2 To 2000
If ws1.Cells(LN, 30) = "Zertifikat" Then 'Spalte 30 (LN,30)
With ws2
ws1.Range(ws1.Cells(LN, 30), ws1.Cells(LN, 33)).Copy
.Cells(r2, 1).PasteSpecial Paste:=xlValues
r2 = r2 + 1
End With
End If
Next LN
For LN = 2 To 2000
If ws1.Cells(LN, 32).Text = "Wein" Then
With ws3
ws1.Range(Cells(LN, 2), ws1.Cells(LN, 20)).Copy
.Cells(r3, 2).PasteSpecial Paste:=xlValues
ws1.Range(Cells(LN, 22), ws1.Cells(LN, 22)).Copy
.Cells(r3, 22).PasteSpecial Paste:=xlValues
r3 = r3 + 1
End With
ws1.Cells(LN, 2).ClearContents
End If
Next LN
ws1.Activate
Range("A2").Select
Application.CutCopyMode = False
End Sub
Grusse
Karel