creating a page x of x in excel

[Lenny]

I am using Excel as a vehicle for a company form for a number of reasons.
Due to the design and layout of the 'protected form' I cannot use the built
in page x of x in the header/footer.

The first page (i.e., worksheet) is static and has a page x of x. The
second worksheet is a continuation sheet with links from the first worksheet
also with a page x of x. This sheet can be copied (duplicated) to create
additional continuation sheets and the order from this sheet to the end could
also be changed. Is there a way this can be accomplished? Perhaps without
VB? I am familiar with, and use VB in protected Word form templates but not
how VB is called and/or used in Excel.

The page x of x on the continuation sheets needs to be able to insert the
sheet number in its correct position as well as count all the sheets in the
workbook. Is this possible to any extent?.... or do I have to instruct the
user that this information must be added manually? I have read the postings
and can't find any question like this.

Regards and thanks for any assistance.

Lenny

 

[Steve Yandl]

Lenny,

The sub below might give you an idea. It may not match your scenario
exactly but it should give you a start. When run, it determines the total
sheets that will be printed in the entire workbook and then goes through
each sheet and inserts Page x of y as the left footer.

'------------------------------
Sub PageThisBook()
Dim PagesThisSheet As Long
Dim PagesSoFar As Long
Dim tmpPages As Long
Dim sht As Worksheet
Dim TotalPages As Long

'Find total pages before starting
TotalPages = 0
For Each sht In Worksheets
sht.Activate
PagesThisSheet = ExecuteExcel4Macro("Get.Document(50)")
TotalPages = TotalPages + PagesThisSheet
Next sht

'Do the page numbering
PagesSoFar = 1
For Each sht In Worksheets
sht.Activate
PagesThisSheet = ExecuteExcel4Macro("Get.Document(50)")

For tmpPages = 1 To PagesThisSheet
With sht.PageSetup
.LeftFooter = "Page &P" & " of " & TotalPages
.FirstPageNumber = PagesSoFar
End With
Next
PagesSoFar = PagesSoFar + PagesThisSheet

Next
End Sub

'------------------------------

Steve Yandl