Ward off printing headaches by taking control of your page breaks

A spreadsheet that looks great onscreen can be a nightmare when you try to print it. Excel tends to have a mind of its own when it comes to setting page breaks, but you can gain the upper hand and avoid unpleasant printing surprises with a macro that inserts page breaks right where you want them — then hunts down and eliminates any automatic page breaks that Excel sneaks in .

To insert page breaks based on our criteria and make sure no automatic page breaks crash the party, we’ll:

•       Set up dynamic page breaks through our VBA macro.

•       Include code to clear all existing page breaks so we can start fresh every time.

•       Build a dialog box that allows the user to lower the zoom percentage and eliminate automatic page breaks.

•       Put all the pieces together into one central macro.

Printing a spreadsheet so that it looks the way you want it is no easy task, especially if you want your page breaks to correlate with changes in your data. You can manually drag page breaks in Page Break View, but you’ll have to change them each time you update your data. And Excel still adds its own automatic page breaks if the data won’t fit within those page breaks at its current zoom percentage. Our VBA solution can give you the control you need to print spreadsheets that adjust with your data.

Identify the problem

Our sample spreadsheet includes projected monthly sales for several products from July 2007 through July 2009, as shown in Figure A. We update the data each month so that there’s a rolling, up-to-date two-year projection.

A:

We want to insert a vertical page break when there’s a change in year.

 

We want the spreadsheet’s printout to break at each change in year so that a year’s data always fits on one page, as shown in Figure B. To accomplish this layout, we’ll need dynamic vertical page breaks because we constantly add and change the worksheet’s columns.

We’re also anticipating another problem: Excel includes automatic page breaks if data doesn’t fit within the manual page breaks. If you reduce the zoom percentage, these automatic page breaks will disappear because the smaller text has a better chance of fitting within your manual page breaks. However, you can’t simply delete automatic page breaks.

Solution: Our macro will check for automatic vertical page breaks. If it finds one, it will display a dialog box (that we create) prompting the user to lower the zoom percentage. The macro keeps the dialog box open until the zoom percentage that the user enters eliminates all automatic page breaks.

B:

Our macro inserts dynamic vertical page breaks so that each year’s worth of data fits on one page wide.

  

Set up dynamic page breaks

We’ll break down this task into several smaller macros — and then combine them so they work together. First, let’s create a procedure that sets up our dynamic page breaks.

  

To create a procedure for inserting dynamic page breaks:

1.          Launch Excel and open our sample file, or create a worksheet similar to the one in shown in Figure A.

2.          Press [Alt][F11] to open the VBE (Visual Basic Editor).

3.          Choose Insert | Module to open a blank code window.

4.          Insert the code in Listing A.

 

Listing A:

Code to create vertical page breaks at each change in year

 

Option Explicit

Sub SetManualPBs()

Cells(1, 2).Activate

Do

If IsDate(ActiveCell) And IsDate _

   (ActiveCell.Offset(0, -1)) Then

    If Year(ActiveCell) = Year _

     (ActiveCell.Offset(0, -1)) Then

     ActiveCell.Offset(0, 1).Select

    Else

     ActiveWindow.SelectedSheets. _

       VPageBreaks.Add Before:=ActiveCell

     ActiveCell.Offset(0, 1).Select

    End If

Else

   ActiveCell.Offset(0, 1).Select

End If

Loop Until IsEmpty(ActiveCell)

End Sub

 

SetManualPBs() begins at cell B2, which is our Price header. A Do...Loop cycles through all of the cells in this row until it reaches an empty cell. As it cycles through, it checks for two conditions at each cell. The first If statement tests the active cell to see if it’s a date (that is, if it’s formatted as a date) and if the cell to its left is also a date. If both those conditions are met, the second If statement checks to see if the years of the two cells are equal; otherwise the macro selects the cell to the right of the active cell and continues the loop. When the years aren’t equal, the macro inserts a vertical page break before the active cell, marking the year change. Otherwise, the loop continues by selecting the cell to its right and repeating the process.

 

Clear all current page breaks

At some point in our macro, we’ll need to make [...]