Excel.Tips.Net ExcelTips (Menu Interface)

Resetting Page Setup

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Resetting Page Setup.

Lori wrote concerning a problem she was having with Excel. It seemed that the page setup for every worksheet in every workbook had changed. Worksheets that previously printed on a single page no longer fit on one page, instead printing on two.

The most likely explanations for behavior such as this is that something has changed in relation to how you print your worksheets. I don't mean that you have gone in and changed your page setup—I mean that you have physically changed a printer on your system or that the printer driver used by your system has been changed. Making such changes can universally affect your worksheets.

It is also possible that the change is due to a change in your version of Excel. If you recently upgraded to a different version, then worksheets could be rendered differently by Excel than they used to be.

Unfortunately, the only way to solve this issue—regardless of the cause—is to manually go through each workbook and change the page setup information for each worksheet. It is time consuming, but the only solution available.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2578) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Resetting Page Setup.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

David Ruben    17 May 2014, 08:19
Of course if frequently take the spreadsheet between different computers or share between people, or as in my case adding or deleting rows destroys carefully crafted page splits along alphabetical entry in column A... then a macro can reset page breaks and the PrintArea; this is triggered by the Workbook_BeforePrint event. The following has constants to set PrintArea end column (presumes starts from column "A", and in example to column "H"), number of title rows at the top (e.g. 5), and the number of rows that will generally always fit depending on font a& fontsize (46 in this example with no enlarged top header text)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Const iPageRows As Integer = 46
    Const iTitleRows As Integer = 5
    Const sEndColumn As String = "H"
    Dim iBodyRows As Integer, iTopRow As Integer, iRow As Integer
    On Error GoTo L_Quit

    'Body of text is iPageLength less the iTitleRows rows
    iBodyRows = iPageRows - iTitleRows
    'Temporarily turn off screen updating
    Application.ScreenUpdating = False
    'Clear old pagebreaks
    'Start with row after the header
    iRow = iTitleRows + 1
    'Set Top of current page row
    iTopRow = iRow
    'Work through rows to add page breaks
    iRow = iRow + 1
    'check if reached end of table of data
    If Range("A" & iRow) = "" Then GoTo L_Completed
    'Pagebreak if new alphabet letter in column A or maximum lines per page
    If UCase(Left(Range("A" & iRow), 1)) <> UCase(Left(Range("A" & iRow - 1), 1)) _
    Or iRow - iTopRow > iBodyRows Then _
        Rows(iRow & ":" & iRow).Select: _
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell: _
        GoTo L_TopPage
    GoTo L_Loop
    'Set the PrintArea and Title rows
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$" & iTitleRows
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1:$" & sEndColumn & "$" & (iRow - 1)
    Application.ScreenUpdating = True
End Sub


Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.