?
Solved

worksheet "Page x of y Pages"

Posted on 2010-01-07
13
Medium Priority
?
922 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:dcantel1
  • 6
  • 6
13 Comments
 
LVL 12

Accepted Solution

by:
kgerb earned 2000 total points
ID: 26204410
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
 
LVL 13

Expert Comment

by:ioane
ID: 26204560
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
 

Author Comment

by:dcantel1
ID: 26206476
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Expert Comment

by:ioane
ID: 26206583
Press [Ctrl] + [Alt] + [F9]
0
 

Author Comment

by:dcantel1
ID: 26206621
When I Press [Ctrl] + [Alt] + [F9] all sheets become the same number for example "Page 1 of 5 Pages".

Thanks

Dennis
0
 
LVL 13

Expert Comment

by:ioane
ID: 26206699
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
 

Author Comment

by:dcantel1
ID: 26206858
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
 
LVL 13

Expert Comment

by:ioane
ID: 26206864
No, you still need to manually recalculate with [Ctrl] + [Alt] + [F9].
0
 

Author Comment

by:dcantel1
ID: 26206879
Ok Thanks
0
 

Author Comment

by:dcantel1
ID: 26206892
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
 
LVL 13

Expert Comment

by:ioane
ID: 26206931
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
 

Author Comment

by:dcantel1
ID: 26206982
Ok very nice,

dcantel1
0
 
LVL 13

Expert Comment

by:ioane
ID: 27658897
Hi dcantel1,

This question is still open, do you need more help with it?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

580 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