Page Numbers

Question

Please help?

How do I enter a page number in a sheet, but not in a header or footer? I have read all the areas of interest, but they all say that it concerns header and footers.

I really am stuck here!

Answer 1

You can use following custom code in your header / footer to enter Sheet Name (not sheet number) to avoid using VBA.

&[Tab]

If you want to insert sheet number, then VBA is the only way out.

Answer 2

Hi beetleboy

Thanks for posting the thread on our forum.

As per your description, please don’t worry we can work together to narrow down and resolve the situation.

Based on your description, it seems that  you want  to enter a page number in a sheet but not use the header or footer.

Generally, the page number are all inserted with header or footer.

To achieve your requirement, you may need to use VBA code:

1. Open the Excel sheet>click Developer ribbon>Visual Basic. To add the Developer ribbon, click File>Options>Customize Ribbon>select Developer in the right panel.
2. In VBA editor>right-click ThisWorkbook>Insert>Module.
3. Copy and paste the following VBA code:

Sub pagenumber()

'updateby Extendoffice 20160506

    Dim xVPC As Integer

    Dim xHPC As Integer

    Dim xVPB As VPageBreak

    Dim xHPB As HPageBreak

    Dim xNumPage As Integer

    xHPC = 1

    xVPC = 1

    If ActiveSheet.PageSetup.Order = xlDownThenOver Then

        xHPC = ActiveSheet.HPageBreaks.Count + 1

    Else

        xVPC = ActiveSheet.VPageBreaks.Count + 1

    End If

    xNumPage = 1

    For Each xVPB In ActiveSheet.VPageBreaks

        If xVPB.Location.Column > ActiveCell.Column Then Exit For

        xNumPage = xNumPage + xHPC

    Next

    For Each xHPB In ActiveSheet.HPageBreaks

        If xHPB.Location.Row > ActiveCell.Row Then Exit For

        xNumPage = xNumPage + xVPC

    Next

    ActiveCell = "Page " & xNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")

End Sub

4. Close the editor and return to the sheet.
5. Select a cell where you want to insert page number. Click Developer>Macros>select that macro>Run. You will see the page number is inserted to that cell.

Hope the suggestion can help you.

If my understanding is incorrect, please feel free to post back.

Regards,

Stacey

Answer 3

Hi Vijay, Thank you for the prompt reply.

The sheet that I'm working on has a section which states "page no **** of ****"

Now the total number of sheets is easy to do, however, I'm really struggling with the sheet number.

Please can you advise/help??

Thanks

John

Answer 4

Header/Footer is the recommended way as just by inserting custom code, page numbering can be accomplished.

What is the business case where you don't want to use header/footer?

If you don't intend to do header/footer stuff, then you will have to use a cell to hold this information. Either you will need to do it manually (in this case, page numbers won't be automatic) or use VBA.

If you want to use a VBA code, let me know. I can write one for you in that case.