Automatic Printing to PDF: Challenges with Repeating Headers and Page Breaks

So far, on this latest project, I have figured out how to handle varying spelling of state names when a normal VLOOKUP or pivot table won't do. And I got through the issue of varying row heights in the downloads. The posts can be found below and each contains a copy of the Excel file so I won't rehash those:

This post will focus on printing your report. The uploaded Excel below does not contain all of the macros or the download tabs for 3 reasons: 1) macros can contain viruses and I don't want to accidentally be carrying viruses; 2) due to reason #1, I don't think WordPress allows you to upload files with macros; and 3) as for the download tabs, my company may have paid for these information and for at least one of the downloads, I could not readily find the information in one place. The minimum wage

There were two major issues when it came to printing the report out to PDF: 1) appropriate page breaks so that I could keep sections together without them being broken up in odd spots and 2) for sections that were especially long (California's Minimum Wage section had 32 rows, each of varying heights so it ran multiple pages), I needed to carry over column headers over to multiple pages. These challenges took me about 2 weeks to figure out and Googling didn't really help this time.

Repeating Column Headers Restricted to a Few Pages, Not Entire Document

Let's start with a section running to multiple pages such that repeating column headers were required. It turns out California's Minimum Wage section was the only section and only state to have that problem, which was good because my solution was inelegant. I did quite a few Googling and I couldn't find an answer to the situation where not all pages were to have the repeating column headers, just a few. I kept finding the basic answer which could be found by using a macro recorder but nothing that would constrain repeating headers to a few pages. So after much thinking, I finally decided to separate out the Compensation section (which would include Minimum Wage, Final Wage Pay and Overtime) into a separate tab during the printing process.

So, the whole process for all of the states starts out with making a temporary tab called "TempReport". All formulas would be stripped out. It is in this tab that additional work are performed to figure out the appropriate page breaks and it is this tab that is then printed out to PDF. California had the additional creation of "TempReport2" where the Compensation section would lie (therefore, the Compensation section would be deleted from "TempReport") and where the repeating headers would be created.

So the key to making repeating headers restrained to certain pages was by separating out the key sections into a different tab and work from there. Fortunately, in this instance, the problem was with just one state and just one section. I don't know how I would have done it with multiple states and multiple sections. Probably use more tabs.

Figuring Out Appropriate Page Breaks

This puzzle required a lot of trial and error. I found after a couple of tries that printing at 63% seemed to keep the information with proper boundaries. Once I had that decided, it was time to figure out how much row height (not rows) can be printed in one page. This is step 1: step 1 puts a formula under column AH that determines the row height for that row. The formula is copied down to the end of the report. Once I had the formulas written down via macro, I let the macro figure out the page breaks itself. That means steps 3-6 were marked inactive. I think it's safe to pick up at step 7. Then I looked at the resulting PDF to see where Excel had put the page breaks and then went back to column AH to determine the total row height being printed per page. Once I had that number, I used that number for MaxHeight.

The next piece of the page breaks puzzle is determining where you want page breaks to go. For me, I wanted page breaks to fall in between each sections. Here is where it got tricky: if I did not have my "fuzzy logic" (represented by various ElseIfs and i+1 or i-1), the page breaks could either create a totally blank page in the middle of the PDF or too much space would be created at the bottom of one or more pages when another section could have fit comfortably within that white space. What I ended up doing was deciding that each section would be separated by 2 rows with row heights of 15. No other rows would have that height. Column B is where I put information of what the row height should be for those rows that do not contain information from the downloads (mainly for header rows and spaces between sections or spaces within section). The combination of the "fuzzy logic" and the row height specification help determine where to put the page breaks. A strict reliance on the MaxHeight variable was not good enough.

Playing With the Margins

Even with the MaxHeight and "fuzzy logic", the printing did not work perfectly so I had to play with the margins (which necessitated figuring out a new MaxHeight). I started out with margins of .75 for both top and bottom but decided .50 worked best

In Conclusion

Printing to a PDF will require some trial and errors which are: 1) percentage fit for printing; 2) the proper MaxHeight (total row heights being crammed in a page); and the margins. You will have to decide what will be the marker for your macro for placing the page breaks: mine was separating the sections with 2 empty rows set with heights of 15. No other blank rows would have a height of 15. Finally, if you want repeating column headers, you will have to separate out that section in another tab - at least I couldn't find any other solution - and print to PDF from there.

A copy of the full macro is shown below so you can see the logic as well as other items such as the code for actually saving to PDF. The first macro, CreateAndPrintReport, prints to PDF according to decisions made in the tab "StartHere". It takes which states you want to print, performs a filter for the state you want, performs Autofitting of rows (that macro is found in my earlier posts) and then goes into printing to PDFs via macro PrintReport. The second macro is of course the PrintReport, which is the bulk of the printing process.

By the way, The Excel that I uploaded above is set to California and does not contain any formulas or macros, so if you were to try to change the state in cell C3 in "Template" tab, it won't work. I stripped out the formulas because I also took out the download tabs.

[ap_divider color="#CCCCCC" style="solid" thickness="3px" width="100%" mar_top="20px" mar_bot="20px"]

Macros Section

Sorry, I can't make the indenting work.

Macro 1: CreateAndPrintReport and ClearChoices

Sub CreateAndPrintReport()

'Button 8 is attached to this macro.
'This one takes the selection(s) in "StartHere" tab and makes the proper selections, apply the row heights and filtering and then print PDF for each selection
Dim StateName As String

If Range("All") = "x" Then
For i = 13 To 32
StateName = Range(Cells(i, 4), Cells(i, 4))
Sheets("Template").Range("C3") = StateName
MainAutofittingRows
PrintReport
Next i

For i = 13 To 32
StateName = Range(Cells(i, 8), Cells(i, 8))
Sheets("Template").Range("C3") = StateName
MainAutofittingRows
PrintReport
Next i

For i = 13 To 23
StateName = Range(Cells(i, 12), Cells(i, 12))
Sheets("Template").Range("C3") = StateName
MainAutofittingRows
PrintReport
Next i
Else
For i = 13 To 32
If Range(Cells(i, 3), Cells(i, 3)) = "x" Then
StateName = Range(Cells(i, 4), Cells(i, 4))
Sheets("Template").Range("C3") = StateName
MainAutofittingRows
PrintReport
End If
Next i

For i = 13 To 32
If Range(Cells(i, 7), Cells(i, 7)) = "x" Then
StateName = Range(Cells(i, 8), Cells(i, 8))
Sheets("Template").Range("C3") = StateName
MainAutofittingRows
PrintReport
End If
Next i

For i = 13 To 23
If Range(Cells(i, 11), Cells(i, 11)) = "x" Then
StateName = Range(Cells(i, 12), Cells(i, 12))
Sheets("Template").Range("C3") = StateName
MainAutofittingRows
PrintReport
End If
Next i
End If

End Sub

Sub ClearChoices()
For i = 13 To 32
Range(Cells(i, 3), Cells(i, 3)).ClearContents
Range(Cells(i, 7), Cells(i, 7)).ClearContents
Range(Cells(i, 11), Cells(i, 11)).ClearContents
Next i
End Sub

Macro 2: PrintReport

Sub PrintReport()

'**************************************************************************
'For creating markers in Steps 4 and 5
Dim FindSection As Variant
Dim FirstRow As Integer
Dim LastRow As Integer
Dim LastRowCond As Integer 'for marking out the conditional formatting
'*************************************************************************

'***************************************************************************
'For setting out the page break markers in Step 6.a
Dim SecRowNo As Integer '(Row number of the starting of a section)
Dim CumRowHeight As Integer
Dim MaxHeight As Integer

MaxHeight = 871 'determines how many rows per page to print It seems to be 871.75 row height for printing at 63%, landscape, 50% margins top and bottom
'****************************************************************************
Dim PDFName As String

'Create two temporary tabs to holds parts of the report to be printed. Minimum wage requires repeating headers because of overflow into multiple pages (California a big reason)
'"TempReport" will hold the all reports except compensation and "TempReport2" will hold the compensation/minimum wage.

'STEP 1: Mark out the row heights - Critical that the endpoint (for i) is exactly at end of report.
For i = 1 To 149
Sheets("Template").Range(Cells(i, 34), Cells(i, 34)) = Sheets("Template").Range(Cells(i, 34), Cells(i, 34)).RowHeight '(Column AH)
If (Sheets("Template").Range(Cells(i, 2), Cells(i, 2)) >= 19 And Sheets("Template").Range(Cells(i, 2), Cells(i, 2)) <= 20) Or i = 1 Then
Sheets("Template").Range(Cells(i, 35), Cells(i, 35)) = "sec"
End If
Next i

'STEP 2: Create "TempReport"
' 2.a Create "TempReport" holding sheet
Sheets("Template").Copy After:=Sheets("Template")
ActiveSheet.Name = "TempReport"

' 2.b Unfilter everything in order for copy/paste special values to work (want to get rid of the formulas before doing any work)
ActiveSheet.Range("$A$7:$A$163").AutoFilter Field:=1

' 2.c Now copy and paste special as values
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' 2.d Filter everything again
Sheets("TempReport").Range("$A$7:$A$163").AutoFilter Field:=1, Criteria1:="<>x"

'Create TempReport2 only for California; STEPS 3 - 5 - has to do with California
'STEP 3: Create "TempReport2"
If Sheets("Template").Range("C3") = "California" Then
Sheets("TempReport").Copy Before:=Sheets("TempReport")
ActiveSheet.Name = "TempReport2"

' STEP 4: Create markers as to where to divvy up the report into "TempReport" and "TempReport2"
' Make "TempReport" hold all other reports aside from Compensation.
' "TempReport2" will hold only compensation.

' 4.a Unfilter everything in "TempReport" in order to find our markers
Sheets("TempReport").Range("$A$7:$A$163").AutoFilter Field:=1

' 4.b Find our markers
Set FindSection = Sheets("TempReport").Range("B:B").Find(What:="mws", LookIn:=xlValues)
FirstRow = FindSection.Row
Set FindSection = Sheets("TempReport").Range("B:B").Find(What:="ov", LookIn:=xlValues)
LastRow = FindSection.Row
Set FindSection = Sheets("TempReport").Range("B:B").Find(What:="mwe", LookIn:=xlValues) 'For deleting conditinal formatting - STEP 5.b
LastRowCond = FindSection.Row

' 4.c Now delete the minimum wage section
Sheets("TempReport").Rows("1:" & LastRow + 2).Delete

' 4.d Filter "TempReport" again
Sheets("TempReport").Range("$A$7:$A$163").AutoFilter Field:=1, Criteria1:="<>x"

' STEP 5: Do proper deletions in "TempReport2" so that what's left is the compensation section.

' 5.a Unfilter "TempReport2"
Sheets("TempReport2").Range("$A$7:$A$163").AutoFilter Field:=1

' 5.b Take off the conditional formatting in minimum wage section before doing any deletions. Conditional formatting contains formulas referencing cells that will be deleted.
Sheets("TempReport2").Range(Cells(FirstRow, 3), Cells(LastRowCond, 4)).FormatConditions.Delete

' 5.d Delete the non-compensation reports (these will be in "TempReport")
Sheets("TempReport2").Rows(LastRow + 2 & ":163").Delete

' 5.e Filter back up "TempReport2"
Sheets("TempReport2").Range("$A$7:$A$163").AutoFilter Field:=1, Criteria1:="<>x"
End If

'STEP 6: Prepare the tabs for printing purposes
' - landscape orientation
' - printing at 63%

' 6.a Section with non-Compensation reports (new hires, EEO, driving, off duty, meal and rest breaks, pregnancy)
Sheets("TempReport").Activate
Application.PrintCommunication = False
With ActiveSheet.PageSetup
' .PrintArea = "$C$1:$O$164"
.Orientation = xlLandscape
.Zoom = 63
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
End With
Application.PrintCommunication = True

'Page breaks
ActiveWindow.View = xlPageBreakPreview
x = Sheets("TempReport").Range("AH:AH").Cells.SpecialCells(xlCellTypeConstants).Count
CumRowHeight = 0
For i = 1 To x
If Range(Cells(i, 35), Cells(i, 35)) = "sec" Then '(Column AI)
SecRowNo = Range(Cells(i, 35), Cells(i, 35)).Row
End If
CumRowHeight = CumRowHeight + Range(Cells(i, 34), Cells(i, 34))
If CumRowHeight > MaxHeight Then
If (Range(Cells(i - 1, 2), Cells(i - 1, 2)) = 15 Or Range(Cells(i - 1, 2), Cells(i - 1, 2)) = "eee") And (Range(Cells(i - 2, 2), Cells(i - 2, 2)) = 15 Or Range(Cells(i - 2, 2), Cells(i - 2, 2)) = "eee") Then
ActiveSheet.HPageBreaks.Add Before:=Rows(i)
CumRowHeight = Application.Sum(Range(Cells(i, 34), Cells(i, 34)))
ElseIf (Range(Cells(i, 2), Cells(i, 2)) = 15 Or Range(Cells(i, 2), Cells(i, 2)) = "eee") And (Range(Cells(i - 1, 2), Cells(i - 1, 2)) = 15 Or Range(Cells(i - 1, 2), Cells(i - 1, 2)) = "eee") Then
ActiveSheet.HPageBreaks.Add Before:=Rows(i)
CumRowHeight = Application.Sum(Range(Cells(i, 34), Cells(i, 34)))
ElseIf (Range(Cells(i, 2), Cells(i, 2)) = 15 Or Range(Cells(i, 2), Cells(i, 2)) = "eee") And (Range(Cells(i + 1, 2), Cells(i + 1, 2)) = 15 Or Range(Cells(i + 1, 2), Cells(i + 1, 2)) = "eee") Then
ActiveSheet.HPageBreaks.Add Before:=Rows(i)
CumRowHeight = Application.Sum(Range(Cells(i, 34), Cells(i, 34)))
Else
ActiveSheet.HPageBreaks.Add Before:=Rows(SecRowNo)
CumRowHeight = Application.Sum(Range(Cells(SecRowNo, 34), Cells(i, 34)))
End If
End If
Next i

' 6.b Section with Compensation reports (minimum wage, overtime, final pay) For California only
If Sheets("Template").Range("C3") = "California" Then
Sheets("TempReport2").Activate
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 63
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintTitleRows = "$11:$12"
End With
Application.PrintCommunication = True
End If

'STEP 7: Select both tabs and save/print as PDF
' 7.a Name file
PDFName = Sheets("Template").Range("C3") & " " & Sheets("Template").Range("I7")

' 7.b Select tabs to print
If Sheets("Template").Range("C3") = "California" Then
Sheets(Array("TempReport2", "TempReport")).Select
Else
Sheets("TempReport").Select
End If

' 7.c If printing all states
If Sheets("StartHere").Range("All") = "x" Then
Application.DisplayAlerts = False
Application.ScreenUpdating = False
FilePath = Sheets("StartHere").Range("F2")
ReportFile = FilePath & PDFName

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=ReportFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Else
'Print and indicate where to save
ReportFile = Application.GetSaveAsFilename(InitialFileName:=PDFName, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Path and FileName to save")

' For when you are not suggesting a filename but leaving it open to the user
' ReportFile = Application.GetSaveAsFilename(InitialFileName:="", _
' FileFilter:="PDF Files (*.pdf), *.pdf", _
' Title:="Select Path and FileName to save")

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=ReportFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End If

'STEP 8: Delete temporary files
Application.DisplayAlerts = False
If Sheets("Template").Range("C3") = "California" Then
Sheets(Array("TempReport", "TempReport2")).Select
Else
Sheets("TempReport").Select
End If
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True

'STEP 9: Go back to "Template" tab
Sheets("Template").Activate
Range("AH:AI").ClearContents
Sheets("StartHere").Activate
Range("C1").Select

End Sub

Automatic Printing to PDF: Challenges with Repeating Headers and Page Breaks

Sample Excel File