Insert page number in active cell instead of in footer

Hi Hsg1,

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

1. Close the editor and return to the sheet.
2. 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.

Best regards,

Tim

Seems you want to show the sheet sequence and total number of sheets. In this case, you can put following as =ShowPage() in a sheet and it will show you 4/7 or 4/6 kind of things.

Function ShowPage()

    Application.Volatile

    ShowPage = Application.Caller.Worksheet.Index & "/" & Worksheets.Count

End Function

If these are pages, then you can accomplish the same through a small VBA code but this will not be a Function but a Sub and can write in an appropriate cell. Let the group know if this is the requirement.

Showing page number/no. of pages using Header / Footer is known.

Requirement is to show it inside active portion of worksheet, not header or footer.

Does your reply mean, using formula etc. ; we can not show page numbers on active portion of worksheet?

regards,

The code gives "page 1 of 1" in each worksheet.

I have six or seven worksheets, where I have one page each and want to print them

one after other in a report. In case of 7 sheets, the display should be "page 1 of 7",

"page 2 of 7" etc...

Does this help: Insert page numbers on worksheetsNote: If you want numbers shown on pages when you print a Microsoft Excel worksheet, you can insert page numbers in the headers or footers of the worksheet pages. Page numbers that you insert are not displayed on the worksheet in Normal view— they are shown only in Page Layout view and on the printed pages.