# Help with Page Numbering in Excel



## texboy (Nov 20, 2003)

Hello,

I have a workbook of 11 worksheets: One Cover, and 1 thru 10.
When I select sheets 1 thru 10, and print them, they are fine (e.g., page 1 of 10, page 2 of 10 etc), but when I select all the sheets including the Cover page, the packet of sheets (1 thru 10) shows page 1 of page 11 etc... and the last page shows page 10 of 11. Obviously, it's counting the Cover page, but that's what I don't want. The Cover page is not set to display the page number.

I there a way to make it like in Word where all the pages are numbered, but not the Cover page?

If anybody can help with this, I will appreciate it.


----------



## David M58 (May 20, 2008)

You could try having Excel modify the page footers before they are printed. Here's an example.

Suppose the first sheet in your workbook is named Cover. Place the code below under ThisWorkbook:


```
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim Sht As Object
    Dim cover_selected As Boolean
    Dim selected_page_count As Long
    Dim cover_page_count As Long
    Dim print_page_count As Long
    
    For Each Sht In ActiveWindow.SelectedSheets
        selected_page_count = selected_page_count + (Sht.HPageBreaks.Count + 1) * (Sht.VPageBreaks.Count + 1)
        If Sht.Name = "Cover" Then
            cover_selected = True
            cover_page_count = cover_page_count + (Sht.HPageBreaks.Count + 1) * (Sht.VPageBreaks.Count + 1)
        End If
    Next Sht

    print_page_count = selected_page_count - cover_page_count

    If cover_selected Then
        ActiveWorkbook.Sheets("Cover").PageSetup.FirstPageNumber = 0
    Else
        ActiveWorkbook.Sheets("Cover").PageSetup.FirstPageNumber = xlAutomatic
    End If
    
    For Each Sht In ActiveWorkbook.Sheets
        If Sht.Name = "Cover" Then
            If cover_selected Then
                Sht.PageSetup.FirstPageNumber = 0
            Else
                Sht.PageSetup.FirstPageNumber = xlAutomatic
            End If
        Else
            If cover_selected Then
                Sht.PageSetup.RightFooter = "Page &P of " & print_page_count
            Else
                Sht.PageSetup.RightFooter = "Page &P of &N"
            End If
        End If
    Next Sht
End Sub
```
The code modifies the page footers when you print the workbook. The example above places the modified page footers on the right side of the page.

Modify the code as necessary.


----------



## texboy (Nov 20, 2003)

I'll give it a try tomorrow and post back results.

Thanks David.


----------



## texboy (Nov 20, 2003)

Hello,
Sorry for the late reply, but I been pretty busy...

I think I need a little more help... I copied/pasted code to the "cover" worksheet by: opening Microsoft Visual Basic Editor, is this correct? if so, it didn't work for me. But, Maybe I'm pasting it on the wrong place.

Can you please explain further.

Thanks.


----------



## David M58 (May 20, 2008)

To place the code under ThisWorkbook, first press Alt-F11 to get to the Visual Basic Editor, then Ctrl-R to display the Project Explorer. The Project Explorer lists Microsoft Excel Objects. Double-click the object ThisWorkbook to open the code window. This is where you put the code.


----------



## texboy (Nov 20, 2003)

Thanks David,

Based on your explanation, I was doing it the right way. I tried it again as you mentioned on the previous post, but I still cannot get it to work.

I was going to attach a sample file with the code added, but there seems to be a problem with our compression program and I didn't bring my laptop to work so, I may have to wait until I get home.

Any other suggestions or maybe you can upload a sample file.


----------



## kevinWRVS (Jul 16, 2008)

Here is the method I use. I assume your pages are on separate sheets.
Select page1, On the FILE menu select PAGE SETUP, under the PAGE tab change first page number to 0.
Then select all other pages. On the FILE menu select PAGE SETUP, under the HEADER FOOTER tab select CUSTOM FOOTER then enter the following text into the appropriate box.

Page &[Page] of &[&[Pages]-1&]

When printing select all pages, your first page will then have no number and will be discounted on following pages


Kevin


----------



## texboy (Nov 20, 2003)

kevinWRVS said:


> Here is the method I use. I assume your pages are on separate sheets.
> Select page1, On the FILE menu select PAGE SETUP, under the PAGE tab change first page number to 0.
> Then select all other pages. On the FILE menu select PAGE SETUP, under the HEADER FOOTER tab select CUSTOM FOOTER then enter the following text into the appropriate box.
> 
> ...



This worked great! 

Thanks Kevin. :wave:

Thanks to you too David for spending time helping.


----------



## texboy (Nov 20, 2003)

It's me again,

What if I wanted to add a "table of contents" and another "workbook?" These would have a a different page # (i.e., page a, page b etc.).

I tried messing with the last number (-1&]), but didn't work.

can you help with this?


----------



## texboy (Nov 20, 2003)

texboy said:


> It's me again,
> 
> What if I wanted to add a "table of contents" and another "workbook?" These would have a a different page # (i.e., page a, page b etc.).
> 
> ...



Just posting findings...

Playing with the settings I was able to get it to work (adding two extra workbooks). The problem is that on the third workbook (which is going to be like page b or something alike), it has two pages, if I shorten the workbook to only one page, it works fine, however, I'm going to need both pages in that third workbook.

So, how do I get it to discount one of the workbooks with two pages?

I'm still playing with the file...:4-dontkno


----------



## kevinWRVS (Jul 16, 2008)

The lst number (-1&]) can be changed to (-2&]), (-3&]), etc. This will discount the first 2 or 3 pages respectfully. If you wish to discount pages not at the begining simply increase this number on all pages following the ones you wish to discount.
I'm not sure how to enter letters as page numbering automatically, however they can be entered manually as text. What is needed is a way to enter ascii codes - I'm sure this is possible I shall try to find a solution when I have time.


----------



## texboy (Nov 20, 2003)

kevinWRVS said:


> The lst number (-1&]) can be changed to (-2&]), (-3&]), etc. This will discount the first 2 or 3 pages respectfully. If you wish to discount pages not at the begining simply increase this number on all pages following the ones you wish to discount.
> I'm not sure how to enter letters as page numbering automatically, however they can be entered manually as text. What is needed is a way to enter ascii codes - I'm sure this is possible I shall try to find a solution when I have time.


Yeah, I had figured out (on previous post) just as you mentioned here). But, actually my problem was that I wanted to discount a workbook that has 2 pages (e.g., cover page (1 page), table of contents (1 page) and intro (2 pages). if I use (-3&]) or (-4&])... won't work.
The problem is with "intro (2 pages)." If it is only 1 page on workbook, it works fine, but not with two.
Any suggestions?

The other problem that came up is that when workbooks are viewed individually, the numbering is messed up (because of the (-2&])) so, my boss and other people involved with the document questioned it (can't make everybody happy. lol). They will have to decide whether we go with this (discounting pages) or just numbering all pages including the cover.
If you have any suggestions on this, let me know too.


----------

