AW: 2 Kriterien prüfen vor kopieren
10.03.2019 22:21:03
AlterDresdner
Hallo Tinchen,
ich hoffe, ich habe richtig verstanden.
Was Du mit dem auskommentierten Code am Ende von kopierensub vorhattest, musst Du selber wissen.
Public Sub kopieren()
kopierensub "Input", 6, "copy", 2
End Sub
Public Sub fresh()
kopierensub "Copy", 2, "Input", 6
End Sub
Sub kopierensub(Quellsheet As String, Quellstart As Long, _
Zielsheet As String, Zielstart As Long)
'Quellstart ist erste Datenzeile in Quellsheet, Zielstart analog
Dim letzteQ As Long, letzteZ As Long, zeile As Long
Dim wsQ As Worksheet, wsZ As Worksheet, found As Object
Dim firstadr As String, mustcopy As Boolean, mustupdate As Boolean
Set wsQ = Worksheets(Quellsheet): Set wsZ = Worksheets(Zielsheet)
Application.ScreenUpdating = False
With wsQ
letzteQ = .Cells(.Rows.Count, 6).End(xlUp).Row
letzteZ = wsZ.Cells(wsZ.Rows.Count, 6).End(xlUp).Row + 1
For zeile = Quellstart To letzteQ
mustcopy = False
mustupdate = False
Set found = wsZ.Range("F:F").Find(what:=.Cells(zeile, 6), _
after:=wsZ.Cells(1, 6), LookIn:=xlValues, lookat:=xlWhole)
If found Is Nothing Then 'noch nicht vorhanden
mustcopy = True
Else 'Bestellnr. in Copy schon da
firstadr = found.Address
If .Cells(zeile, 11) = wsZ.Cells(found.Row, 11) Then 'Kombi stimmt
mustupdate = True
Else 'Kombi stimmt nicht, weitersuchen, ob evtl.
Do
Set found = wsZ.Range("F:F").FindNext(found)
If .Cells(zeile, 11) = wsZ.Cells(found.Row, 11) Then _
mustupdate = True 'Kombi stimmt
Loop Until found.Address = firstadr Or mustupdate
If Not mustupdate Then mustcopy = True
End If
End If
If mustcopy Then 'ganze Zeile kopieren
.Rows(zeile).Copy Destination:=wsZ.Cells(letzteZ, 1)
letzteZ = letzteZ + 1
ElseIf mustupdate Then 'Spalte I-X kopieren
Application.DisplayAlerts = False
.Range(.Cells(zeile, 9), .Cells(zeile, 24)).Copy _
Destination:=wsZ.Cells(found.Row, 9) 'I-X kopieren
Application.DisplayAlerts = True
End If
Next zeile
Application.CutCopyMode = False
End With
With wsZ
letzteZ = .Cells(.Rows.Count, 6).End(xlUp).Row
'.Range(.Cells(1, 24), .Cells(letzteZ, 24)).FormulaLocal = "=ZEILE()"
'.Range(.Cells(1, 24), .Cells(letzteZ, 24)).Value = .Range(.Cells(2, 24), _
.Cells(letzteZ, 24)).Value
.Range("A" & Zielstart & ":Y" & letzteZ).Sort Key1:=.Range("X" & Zielstart), _
Order1:=xlDescending, Header:=xlNo
.Range("A" & Zielstart - 1 & ":Y" & letzteZ).RemoveDuplicates Columns:=6, Header:=xlYes
.Range("A" & Zielstart & ":Y" & letzteZ).Sort Key1:=.Range("Y" & Zielstart), _
Order1:=xlAscending, Header:=xlNo
'.Columns(24).ClearContents
End With
Application.ScreenUpdating = True
Set wsQ = Nothing: Set wsZ = Nothing
End Sub
Gruß der Martin