Private Sub But_dieser_Jahr_Quartal_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Ws As Worksheet, Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet, Ws4 As Worksheet, Ws5 As Worksheet
Dim Mt(), i&
On Error Resume Next
Mt = Array("1.Quartal", "2.Quartal", "3.Quartal", "4.Quartal")
' Sheets herstellen
For i = 0 To UBound(Mt)
Set Ws = Worksheets(Mt(i))
If Err.Number <> 0 Then _
Set Ws = Sheets.Add(After:=Worksheets(Worksheets.Count)): Ws.Name = Mt(i)
Ws.Visible = xlSheetHidden
Err.Clear
Next i
Set Ws = Worksheets(TBL)
Set Ws2 = ThisWorkbook.Worksheets("1.Quartal")
Set Ws3 = ThisWorkbook.Worksheets("2.Quartal")
Set Ws4 = ThisWorkbook.Worksheets("3.Quartal")
Set Ws5 = ThisWorkbook.Worksheets("4.Quartal")
If Err.Number <> 0 Then _
MsgBox "Blatt " & TBL & " nicht gefunden .. ", vbCritical: Exit Sub
' Einsortieren
With Ws
For i = 1 To .Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row
' handelt es sich überhaupt um ein Datum ?
If IsDate(.Cells(i, Range(STARTZELLE).Column).Value) Then
'1 Quartal
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 1 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws2.Cells(Ws2.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 2 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws2.Cells(Ws2.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 3 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws2.Cells(Ws2.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
'2 Quartal
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 4 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws3.Cells(Ws3.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 5 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws3.Cells(Ws3.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 6 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws3.Cells(Ws3.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 7 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws4.Cells(Ws4.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 8 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws4.Cells(Ws4.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 9 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws4.Cells(Ws4.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 10 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws5.Cells(Ws5.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 11 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws5.Cells(Ws5.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
If Year(.Cells(i, Range(STARTZELLE).Column).Value) = Year(Date) And Month(.Cells(i, Range(STARTZELLE).Column).Value) = 12 Then
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).Copy Destination:= _
Ws5.Cells(Ws5.Cells(Rows.Count, Range(STARTZELLE).Column).End(xlUp).Row + 1, 1)
.Range(.Cells(i, 1), .Cells(i, LAST_COL)).ClearContents
End If
End If
Next i
End With
Set Ws = Nothing: Erase Mt: Set Ws1 = Nothing: Set Ws2 = Nothing: Set Ws3 = Nothing: Set Ws4 = Nothing: Set Ws5 = Nothing
Application.ScreenUpdating = True
Module1.dieser_Jahr_Quartal
End Sub