[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Numbering Pages in Excel

Posted on 2011-04-21
13
Medium Priority
?
461 Views
Last Modified: 2012-08-14
Is there a way to number every nth page in the footer in an Excel spreadsheet.  For example, I need to number every 5th page in the footer of a worksheet.  If there are 16 pages, then, on Page 1 the footer will show 1, on Page 6 the footer will show 2, on page 11 the footer will show 3, etc.  Any assistance willl be greatly appreciated.
0
Comment
Question by:vtrinity7
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35441124
you can set the footer using

ActiveSheet.PageSetup.CenterFooter = "this page is page number &P"

Open in new window


the only codes (here : &P = page number) you can use are

&D Prints the current date.
&T Prints the current time.
&F Prints the name of the document.
&A Prints the name of the workbook tab.
&P Prints the page number.
&P+number Prints the page number plus the specified number.
&P-number Prints the page number minus the specified number.
&& Prints a single ampersand.
&N Prints the total number of pages in the document.  
&Z Prints the file path.
&G Inserts an image.
0
 
LVL 19

Accepted Solution

by:
Arno Koster earned 2000 total points
ID: 35441147
so the only option i see is to print using this sequence :

- set footer to "1"
- print page 1
- set footer to ""
- print pages 2 up to 4
- set footer to "2"
- print page 5
- set footer to ""
- print pages 6 up to 9
[...]
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 35441331
I remember a package from Microsoft called Binder where you could combine prints from different applications and set the page numbering accordingly.

Does this still exist?

Cheers
Rob H
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vtrinity7
ID: 35441418
Akoster thank you for your insight.  However, is the code you are providing VBA code.  When I try to cut and paste it in the footer, it returns exactly what I put in.
0
 

Author Comment

by:vtrinity7
ID: 35441745
I've done a little research and have concluded that VBA code is necessary to accomplish this.  The downside is, I'm not familar with VBA code, so I would need assistance with writing the code.  Basically, I need to write VBA code for the page footers what will only show a number every 5th page.  As mentioned above, page 1 would have 1 in the footer, the footer with pages 2-5 will be blankr, page 6 would have a 2 in the footer,the footer for  pages 7-10 would be blank, so on and so forth.  Any suggestions?  
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35441779
The code indeed is VBA code. When you edit the footer yourself, you can use

this page is page number &P

Open in new window

0
 

Author Comment

by:vtrinity7
ID: 35441831
Thanks.  When I enter that, it shows the sequential page numbers for each page.  Keep in mind, I only need to show a number for every 5th  page number.  For example:

Page 1 shows 1 in the footer
Page 3 shows " "
Page4 shows " "
Page 5 shows " "
Page 6 shows 2 in the footer..........

I have a 75 page document, so the last number I am expecting in the footer is 15
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35441862
for the VBA code, you can use

Sub print_with_footers()
Dim nr As Integer

    'loop through all pages
    For nr = 1 To 5
        
        '-- enable or disable footer
        If nr Mod 5 = 1 Then
            ActiveSheet.PageSetup.CenterFooter = Int(nr / 5) + 1
        Else
            ActiveSheet.PageSetup.CenterFooter = ""
            
        End If
        '-- print page
        PrintOut nr
    Next nr
    

End Sub

Open in new window

0
 

Author Comment

by:vtrinity7
ID: 35442043
AKoster, thank you for your efforts.  Unfortunately, I'm still not getting expected results.  The numbers are not displayed in the footer.  Also, since I have a 75 page document, shouldn't the loop be for 1 to 75 instead of 1 to 5?  In addition, I copied and pasted the code provided into a VB Editor, is that the correct place?
0
 

Author Comment

by:vtrinity7
ID: 35442090
Also, I want the page nubmers to show in the footer.  I do not have a need for sending the document to a printer.  I may be wrong, however, I think the code provided sends the document to the printer.
0
 
LVL 12

Expert Comment

by:Amick
ID: 35442206
I thought you could use the beforeprint macro as explained here:
http://www.mrexcel.com/forum/showthread.php?t=409861

The formula would be something like this

=IF(MOD(A1,5) = 1,(FLOOR(A1/5,1)+1),"")

Where A1 would contain the current page number.  

Using VBA I initially tried with the &P variable, but quickly realized that that was just a placeholder filled in later by the print routine and apparently not available to the user.

The second try was a global counter that was updated each time a page was about to print, but that doesn't  work because BeforePrint seems to be executed only once.

There certainly may be an object with the property of current page number that is available to the user, but I haven't found it.

My answer, then, is that it probably can't be done programmatically.
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 35446147
for a 75 page document, the loop should indeed be up to 75 instead of 5.
To prevent unnecessary paper use, I suggest installing and using a pdf printer.

When you set the footer, it will only show when you print the document or preview the printout.
The footer is not visible when you do regular excel work, so if you want to 'see' the footer without printing the document, you will have to place the numbers in arbitrary cells in excel.

Can you clarify what your needs are ?


0
 
LVL 12

Expert Comment

by:Amick
ID: 35446884
You can see the header/footer in Excel 2007, and probably other versions, if you select the page layout view.

You can make akoster's code work for any size worksheet if you change line 5 to
 For nr = 1 To ActiveSheet.PageSetup.Pages.Count

Unfortunately, each time you change the footer, it affects all pages, so you will have to place the numbers in arbitrary cells as akoster advised, but keeping track of where the rows are and what the page sizes are becomes an issue.

You could reserve a column for a formula like: =IF(ROW()/6 =INT(ROW()/6),"Page "& (ROW()/6),"")
In this case 6 is an arbitrary pagesize, that in your case would need to be adjusted to somewhere in the neighborhood of 200-300 to account for 5 pages of output.  Using a variable for pagesize would be a good choice as you'll need to be able to adjust for changes in rowsize and perhaps even output device.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question