worksheet "Page x of y Pages"

Please, I will like to insert the "Page x of y Pages" in individual worksheet cell.

Kapil_119: sent me the following macro, its function ok, but I will like that the "Page x of y Pages" be inserted in individual worksheet cell. The situation is that I have several sheets that I selected the printed area for each one, and each sheet is an individual page.

Thanks you very much, dcantel1

Sub ShowMePage()
Dim mySheet As Worksheet
Dim iHpBreaks As Integer
Dim iTotPages As Integer
Dim inumpage As Integer
Dim pagenumber As Integer
    inumpage = 1
   
    For Each mySheet In Worksheets
        iHpBreaks = mySheet.HPageBreaks.Count + 1
        inumpage = inumpage + iHpBreaks
    Next mySheet
   
    MsgBox inumpage
    pagenumber = 1
    For Each mySheet In Worksheets
        mySheet.PageSetup.CenterHeader = "Page " & pagenumber & " of " & inumpage & " Pages"
        pagenumber = pagenumber + 1
    Next mySheet
   
End Sub
dcantel1Asked:
Who is Participating?
 
kgerbChief EngineerCommented:
dcantel1,
The following code will put the page number in cell A1 of each worksheet.  Modify the location to whichever cell you want the page numbers to be entered.

Kyle
Sub ShowMePage()
Dim mySheet As Worksheet
Dim iHpBreaks As Integer
Dim iTotPages As Integer
Dim inumpage As Integer
Dim pagenumber As Integer
    inumpage = 1
   
    For Each mySheet In Worksheets
        iHpBreaks = mySheet.HPageBreaks.Count + 1
        inumpage = inumpage + iHpBreaks
    Next mySheet
   
    MsgBox inumpage
    pagenumber = 1
    For Each mySheet In Worksheets
        mySheet.Range("A1") = "Page " & pagenumber & " of " & inumpage & " Pages"
        pagenumber = pagenumber + 1
    Next mySheet
   
End Sub

Open in new window

0
 
ioanePlanning & Analytics ManagerCommented:
You could create a custom function.

If you only have 1 printable page per sheet, the code below should do it.

Copy the code into a Module then type:

=xofysheets()

into any cell in your worksheets.


Function XOfYSheets() As String
  XOfYSheets = "Page " & ActiveSheet.Index & " of " & Sheets.Count
End Function

Open in new window

0
 
dcantel1Author Commented:
Tramtrak:

This solution was very nice an easy to understand. The Page x of y Pages was very nice inserted to the sheet cell. Now when I change one sheet from it original position the Page x of y Pages still is the same page number. How can I modify your solution to when I change the sheet from it original position it will be adjusted automatically following the sheets sequence in the workbook file. For example if I change sheet 3 to before sheet 2, sheet 3 will become sheet 2 and sheet 2 will become sheet 3 and so on.

Thanks, dcantel1
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ioanePlanning & Analytics ManagerCommented:
Press [Ctrl] + [Alt] + [F9]
0
 
dcantel1Author Commented:
When I Press [Ctrl] + [Alt] + [F9] all sheets become the same number for example "Page 1 of 5 Pages".

Thanks

Dennis
0
 
ioanePlanning & Analytics ManagerCommented:
Ok, the only way then is to either select each cell individually, press [F2], [Enter] to recalculate that cell after moving sheets or enter the sheet name as part of the formula, as modified below:

Function XOfYSheets(Optional SheetName As String) As String
  If SheetName <> "" Then
    XOfYSheets = "Page " & Sheets(SheetName).Index & " of " & Sheets.Count
  Else
    XOfYSheets = "Page " & ActiveSheet.Index & " of " & Sheets.Count
  End If
End Function

Open in new window

0
 
dcantel1Author Commented:
Its functions better, but if I change of position the sheets sequence the "Page x of y Pages" are not adjusted automatically.

Thanks

dcantel1
0
 
ioanePlanning & Analytics ManagerCommented:
No, you still need to manually recalculate with [Ctrl] + [Alt] + [F9].
0
 
dcantel1Author Commented:
Ok Thanks
0
 
dcantel1Author Commented:
When I recalculate the "Page x of y Pages" change to the same page number to all sheets. For example all sheets are "Page 1 of 5 Pages"........
.
0
 
ioanePlanning & Analytics ManagerCommented:
If you use the new version of the formula I created and add the name of each sheet to it, it will work fine.

eg.
If your sheet is named "Sheet1", then add the formula like this:

=xofysheets("Sheet1")

For a sheet named "My Data Sheet":

=xofysheets("My Data Sheet")

etc.
0
 
dcantel1Author Commented:
Ok very nice,

dcantel1
0
 
ioanePlanning & Analytics ManagerCommented:
Hi dcantel1,

This question is still open, do you need more help with it?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.