AW: Button zum ziehen.Frage
25.01.2017 08:45:28
UweD
Hallo
Matthias hat ja schon Vorarbeit geleistet...
hier noch das Sortieren
Sub MA_verschieben() 'von Uwe D
Dim TB2, LR1 As Long, LR2 As Long
Set TB2 = Sheets("Tabelle2")
LR2 = TB2.Cells(TB2.Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Tabelle1")
If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten
LR1 = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2:B" & LR1).AutoFilter Field:=1, Criteria1:="<>""""", Operator:=xlAnd
.Range("A2:B" & LR1).AutoFilter Field:=2, Criteria1:="<>"
.Range("A3:B" & LR1).Copy TB2.Range("A" & LR2) 'Zielzelle
.Range("A3:B" & LR1).EntireRow.Delete xlUp
.AutoFilterMode = False
TB2.UsedRange.Value = TB2.UsedRange.Value 'ggf Formeln raus
End With
'Sortieren
With TB2
LR2 = .Cells(.Rows.Count, "A").End(xlUp).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("B3:B" & LR2) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=.Range("A3:A" & LR2) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SetRange .Range("A2:B" & LR2)
.Sort.Header = xlYes
.Sort.MatchCase = False
.Sort.Orientation = xlTopToBottom
.Sort.SortMethod = xlPinYin
.Sort.Apply
End With
End Sub
LG UweD