Sub monatrapport_neu_temp()
Dim Zeile As Long
Dim ZeileMax As Long
Dim n As Long
Dim kopf As Long
Dim zeilex As Long
Dim zeilexz As Long
Dim monatstart As Variant
Dim monat As Integer
monat = InputBox("Bitte gewünschten Monat eingeben")
Sheets("BerichtMonat").Select
With Worksheets("BerichtMonat")
Worksheets("BerichtMonat").UsedRange.ClearContents
Tabelle6.Columns("a:z").NumberFormat = "[h]:mm"
End With
With Worksheets("BerichtMonat")
Range("a1:z5000").Interior.Color = vbWhite
Range("a1:z5000").Borders.LineStyle = -4142
Range("A1:z5000").Font.name = "Calibri"
Range("a1:z5000").Font.Bold = False
Range("a1:z5000").Font.Size = 10
kopf = 9
n = 10
zeilex = 0
ZeileMax = .UsedRange.Rows.Count
End With
startdienst:
With Worksheets("BerichtMonat")
Range("a" & kopf - 3).Value = "Name"
Range("b" & kopf - 3).Value = Worksheets("Dienst").Range("b3").Value
Range("a" & kopf - 2).Value = "Monat"
Range("b" & kopf - 2) = MonthName(monat)
Range("d" & kopf - 2).Value = "Jahr"
Range("e" & kopf - 2).Value = "2021"
Range("e" & kopf - 2).NumberFormat = "0000"
End With
With Worksheets("Dienst")
With Worksheets("BerichtMonat").Range("A" & kopf, "a" & kopf)
.Value = "Dienst"
.Font.Size = 13
.Font.Bold = True
.Font.ColorIndex = 1
End With
With Worksheets("BerichtMonat").Range("d" & kopf, "d" & kopf)
.Value = "Beginn"
.Font.Size = 8
.Font.Bold = True
.Font.ColorIndex = 1
End With
With Worksheets("BerichtMonat").Range("e" & kopf, "e" & kopf)
.Value = "Ende"
.Font.Size = 8
.Font.Bold = True
.Font.ColorIndex = 1
End With
With Worksheets("BerichtMonat").Range("f" & kopf, "f" & kopf)
.Value = "Pause"
.Font.Size = 8
.Font.Bold = True
.Font.ColorIndex = 1
End With
With Worksheets("BerichtMonat").Range("g" & kopf, "g" & kopf)
.Value = "Stunden"
.Font.Size = 8
.Font.Bold = True
.Font.ColorIndex = 1
End With
With Worksheets("BerichtMonat").Range("h" & kopf, "h" & kopf)
.Value = "Nacht"
.Font.Size = 8
.Font.Bold = True
.Font.ColorIndex = 1
End With
With Worksheets("BerichtMonat").Range("i" & kopf, "i" & kopf)
.Value = "Sonntag"
.Font.Size = 8
.Font.Bold = True
.Font.ColorIndex = 1
End With
For Zeile = 8 + zeilex To ZeileMax
If Month(.Cells(Zeile, 1).Value) = monat Then
.Range("a" & Zeile, "i" & Zeile).Copy Destination:=Tabelle6.Rows(n)
n = n + 1
End If
If n = 52 Then
n = 57
kopf = 56
zeilex = 42
GoTo startdienst
End If
If n = 101 Then
n = 106
kopf = 105
zeilex = 86
GoTo startdienst
End If
Next Zeile
Tabelle6.Range("g" & n).Value = WorksheetFunction.Sum(Tabelle6.Range("g10", "g" & n))
Tabelle6.Range("h" & n).Value = WorksheetFunction.Sum(Tabelle6.Range("h10", "h" & n))
Tabelle6.Range("i" & n).Value = WorksheetFunction.Sum(Tabelle6.Range("i10", "i" & n))
Tabelle6.Range("g" & n, "g" & n + 1).BorderAround ColorIndex:=0, Weight:=xlThin
Tabelle6.Range("h" & n, "h" & n + 1).BorderAround ColorIndex:=0, Weight:=xlThin
Tabelle6.Range("i" & n, "i" & n + 1).BorderAround ColorIndex:=0, Weight:=xlThin
Tabelle6.Range("g" & n + 1, "i" & n + 1).NumberFormat = "0.00"
Tabelle6.Range("g" & n + 1).Value = Cells(n, 7) * 24
Tabelle6.Range("h" & n + 1).Value = Cells(n, 8) * 24
Tabelle6.Range("i" & n + 1).Value = Cells(n, 9) * 24
Cells(n, 6) = "Summe"
End With
Dim DateiName As String
Dim Datei As String
DateiName1 = Cells(5, 2)
DateiName2 = Cells(6, 2)
Datei = DateiName1 & " Monat " & DateiName2 & ".pdf"
ActiveSheet.PageSetup.PrintArea = "a1:j" & n + 1
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.PageSetup.Orientation = xlPortrait
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Datei, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
Sheets("Dienst").Select
End Sub