Page X of Y Woes

by Allen Wyatt
(last updated December 13, 2008)

1

One of the most frustrating problems in Word 97 always crops up when you are trying to print those cool page numbers in your header or footer. You know--the ones that indicate Page 1 of 432 (or however many pages are in your document). Word even provides an AutoText entry that allows you to easy add the Page X of Y notation in your header or footer.

The problem is that it doesn't work. At least it doesn't work consistently. Even though everything shows fine when you look at the document in Print Preview mode, when you actually print, the second number always shows as 1 or the same number as the current page number. (It does look odd to see Page 78 of 1 on your printout.)

There are three ways you can start to fix this problem, and all of them mean extra work for you. (Perhaps I misspoke; these are workarounds, not fixes.) These three methods are as follows:

  • Turn off Show/Hide either by using the Show/Hide tool on the toolbar, or by using the control on the View tab of the Options dialog box.
  • Turn on reverse print order by using the Reverse Print Order check box on the Print tab of the Options dialog box.
  • Turn off background printing by using the Background Printing check box on the Print tab of the Options dialog box.

Once you have done any of these three, you need to update the misbehaving field in your document. This means that if the field is in a header or footer, you must first display the field. Then right-click on it (right click on the Y part of the Page X of Y portion of the header or footer) and choose Update Field from the resulting Context menu. Now when you print your document, all should work as expected.

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (958) applies to Microsoft Word 97.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Calculating Statistical Values on Different-Sized Subsets of Data

Discovering different ways to analyze your data can be a challenge. Here's how to work with arbitrary subsets of a large ...

Discover More

Calculating Differences in Months using Non-Standard Date Values

Dates can be entered into a worksheet in any number of unique or novel ways. Working with those dates can be a challenge, ...

Discover More

Adding Hyphenated Words to the Dictionary

When you hyphenate words, does the resulting compound word end up being marked as incorrectly spelled? This tip examines ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More WordTips (menu)

Getting Page Number Formatting Changes to Stick

Placing a page number into your document can be easy. Getting that page number to look the way you want it to can be more ...

Discover More

Turning Off Default First Page Numbering

Ever want to change the default settings for how Word handles page numbering? Word doesn't make this as easy as you would ...

Discover More

Adding Page Numbers in Headers or Footers

While Word has a default format for page numbers, you can design and specify how you want them to appear in your ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 7?

2018-08-25 06:10:32

Graham

I had this problem when I worked for a multi-national company and received sales data from over 20 marketing countries.

Often the files contained the default number of worksheets, usually 3, although only Sheet 1 contained the required data.
Sometimes the extra sheets contained useful data and sometimes they were HIDDEN !!

It was a time consuming task to check all of the extra worksheets manually, so I wrote a macro to do this automatically, see below.
At that time I was only interested in normal cell 'data', so the macro does not check for inserted comments, objects, pictures, etc.

I wrote this macro in the 1990s, so probably Excel 95. It still works for Excel 2010.
Since that time I have always started workbooks with only 1 worksheet, more can be added later if needed.

Sub EmptyWorksheets()

'Works in all situations, including for worksheets that have had data deleted BEFORE workbook is saved.
'Searches all worksheets in the currently active workbook, including HIDDEN worksheets.

'Note a cell is NOT empty if the cell contains a formula, even if the formula result is "".
'However, a cell IS empty if it ONLY has an attached comment, object or picture, etc.

Dim foundsheet, lastcell, countvalue, allcells

For Each foundsheet In Worksheets
lastcell = foundsheet.Cells.SpecialCells(xlLastCell).Address
'lastcell may have an higher value than expected if :-
'An empty cell has an attached comment (The cell is used, but defined as EMPTY by this macro).
'Some data has been deleted, but the workbook has not been SAVED, since
'the xlLastCell value is only reset to a LOWER value when the workbook is saved.
'However the xlLastCell value is automatically INCREASED if data is added to new columns/rows

allcells = "$A$1:" & lastcell
'Defines range of cells that may contain data.

countvalue = Application.WorksheetFunction.CountA(Worksheets(foundsheet.Name).Range(allcells))
'CountA function finds cells that contain data.

If countvalue = 0 Then 'No cells in use
If foundsheet.Visible Then
MsgBox "Worksheet # " & foundsheet.Name & " # is EMPTY"
Else 'sheet is hidden
MsgBox "Worksheet # " & foundsheet.Name & " # is HIDDEN and is EMPTY"
End If
Else 'At least 1 cell contains data
If foundsheet.Visible Then
MsgBox "Worksheet # " & foundsheet.Name & " # CONTAINS DATA"
Else 'sheet is hidden
MsgBox "Worksheet # " & foundsheet.Name & " # is HIDDEN and CONTAINS DATA"
End If
End If
Next foundsheet

End Sub


This Site

Got a version of Word that uses the menu interface (Word 97, Word 2000, Word 2002, or Word 2003)? This site is for you! If you use a later version of Word, visit our WordTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.