Option Base 1
Option Explicit
Sub DatenSortiertKopieren()
Call EventsOff
Dim Zaehler1 As Long, Zaehler2 As Long, Zeile1 As Long, Zeile2 As Long
Dim WksName As String, Text1 As String, Text2 As String
Worksheets("Daten").Activate
ReDim ArrQ(Worksheets("Daten").Cells(Rows.Count, 1).End(xlUp).Row, 2) As Variant
ArrQ() = Range("A1:B" & Worksheets("Daten").Cells(Rows.Count, 1).End(xlUp).Row)
For Zaehler1 = 2 To UBound(ArrQ())
If UCase(ArrQ(Zaehler1, 1)) = "SCHMIERANWEISUNG" Then
WksName = "Schmierung"
Zaehler1 = Zaehler1 + 2
Zeile1 = Zaehler1
End If
If UCase(ArrQ(Zaehler1, 1)) = "WARTUNGSANWEISUNG" Then
WksName = "Wartung"
Zaehler1 = Zaehler1 + 2
Zeile1 = Zaehler1
End If
If UCase(Mid(ArrQ(Zaehler1, 1), 1, 7)) = "LEGENDE" Or UCase(Mid(ArrQ(Zaehler1, 1), 1, 9)) = "BEMERKUNG" Then
Zeile2 = Zaehler1 - 2
Worksheets("Daten").Rows(Zeile1 & ":" & Zeile2).Copy _
Worksheets(WksName).Cells(Worksheets(WksName).Cells(Rows.Count, 2).End(xlUp).Row + 1, 1)
WksName = ""
End If
Next Zaehler1
Worksheets("Schmierung").Activate
ArrQ() = Range("A1:A" & Worksheets("Schmierung").Cells(Rows.Count, 2).End(xlUp).Row)
For Zaehler1 = 2 To UBound(ArrQ())
If ArrQ(Zaehler1, 1) <> "" And ArrQ(Zaehler1, 1) <> Text1 Then
Text1 = ArrQ(Zaehler1, 1)
Zaehler2 = 2
End If
If ArrQ(Zaehler1, 1) = "" Then
If Len(CStr(Zaehler2)) = 1 Then Text2 = Mid(Text1, 1, Len(Text1) - 3) & "00" & CStr(Zaehler2)
If Len(CStr(Zaehler2)) = 2 Then Text2 = Mid(Text1, 1, Len(Text1) - 3) & "0" & CStr(Zaehler2)
Zaehler2 = Zaehler2 + 1
ArrQ(Zaehler1, 1) = Text2
End If
Next Zaehler1
Range("A1:A" & Worksheets("Schmierung").Cells(Rows.Count, 2).End(xlUp).Row) = ArrQ()
Worksheets("Wartung").Activate
ArrQ() = Range("A1:A" & Worksheets("Wartung").Cells(Rows.Count, 2).End(xlUp).Row)
For Zaehler1 = 2 To UBound(ArrQ())
If ArrQ(Zaehler1, 1) <> "" And ArrQ(Zaehler1, 1) <> Text1 Then
Text1 = ArrQ(Zaehler1, 1)
Zaehler2 = 2
End If
If ArrQ(Zaehler1, 1) = "" Then
If Len(CStr(Zaehler2)) = 1 Then Text2 = Mid(Text1, 1, Len(Text1) - 3) & "00" & CStr(Zaehler2)
If Len(CStr(Zaehler2)) = 2 Then Text2 = Mid(Text1, 1, Len(Text1) - 3) & "0" & CStr(Zaehler2)
Zaehler2 = Zaehler2 + 1
ArrQ(Zaehler1, 1) = Text2
End If
Next Zaehler1
Range("A1:A" & Worksheets("Wartung").Cells(Rows.Count, 2).End(xlUp).Row) = ArrQ()
Call EventsOn
End Sub