I am trying to put sequential page numbers on each worksheet for the
worksheet and also put sequential page number for the entire workbook. So
the print out for worksheet 1 has Page 1 of 1 and also Page 1 of 45 on the
same page. Can anyone tell me if this is possible and how I do it?

I think this will do what you are asking. Put the code in the ThisWorkBook
module in the VBA editor.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
Dim PgCnt As Long
Dim PgCntTotal As Long
Dim lngTemp As Long

Application.ScreenUpdating = False

lngTemp = ActiveSheet.Index
For i = 1 To Sheets.Count
Sheets(i).Activate
If i < lngTemp Then _
PgCnt = PgCnt + ExecuteExcel4Macro("Get.Document(50)")
PgCntTotal = PgCntTotal + ExecuteExcel4Macro("Get.Document(50)")
Next i

Sheets(lngTemp).Activate
With ActiveSheet.PageSetup
.RightFooter = "&P Of &N" & Chr(10) & "&P+" & PgCnt & " Of " & PgCntTotal
End With

Application.ScreenUpdating = True

End Sub

"Puzzled" wrote:

I am trying to put sequential page numbers on each worksheet for the
worksheet and also put sequential page number for the entire workbook. So
the print out for worksheet 1 has Page 1 of 1 and also Page 1 of 45 on the
same page. Can anyone tell me if this is possible and how I do it?

After thinking about it, that won't work properly if you select multiple
sheets to be printed or want to print the entire workbook at once as the
macro only modifies the footer on the activesheet (but printing out one sheet
at a time should work okay). I think it would be a little tricky to
accomodate all scenarios (ie printing one sheet, printing multiple sheets,
printing entire workbook).

"JMB" wrote:

I think this will do what you are asking. Put the code in the ThisWorkBook
module in the VBA editor.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
Dim PgCnt As Long
Dim PgCntTotal As Long
Dim lngTemp As Long

Application.ScreenUpdating = False

lngTemp = ActiveSheet.Index
For i = 1 To Sheets.Count
Sheets(i).Activate
If i < lngTemp Then _
PgCnt = PgCnt + ExecuteExcel4Macro("Get.Document(50)")
PgCntTotal = PgCntTotal + ExecuteExcel4Macro("Get.Document(50)")
Next i

Sheets(lngTemp).Activate
With ActiveSheet.PageSetup
.RightFooter = "&P Of &N" & Chr(10) & "&P+" & PgCnt & " Of " & PgCntTotal
End With

Application.ScreenUpdating = True

End Sub

"Puzzled" wrote:

I am trying to put sequential page numbers on each worksheet for the
worksheet and also put sequential page number for the entire workbook. So
the print out for worksheet 1 has Page 1 of 1 and also Page 1 of 45 on the
same page. Can anyone tell me if this is possible and how I do it?

Forgot to thank Mr. Walkenbach for his tip on getting the number of printed
pages

http://www.j-walk.com/ss/excel/tips/tip65.htm

"JMB" wrote:

I think this will do what you are asking. Put the code in the ThisWorkBook
module in the VBA editor.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
Dim PgCnt As Long
Dim PgCntTotal As Long
Dim lngTemp As Long

Application.ScreenUpdating = False

lngTemp = ActiveSheet.Index
For i = 1 To Sheets.Count
Sheets(i).Activate
If i < lngTemp Then _
PgCnt = PgCnt + ExecuteExcel4Macro("Get.Document(50)")
PgCntTotal = PgCntTotal + ExecuteExcel4Macro("Get.Document(50)")
Next i

Sheets(lngTemp).Activate
With ActiveSheet.PageSetup
.RightFooter = "&P Of &N" & Chr(10) & "&P+" & PgCnt & " Of " & PgCntTotal
End With

Application.ScreenUpdating = True

End Sub

"Puzzled" wrote:

I am trying to put sequential page numbers on each worksheet for the
worksheet and also put sequential page number for the entire workbook. So
the print out for worksheet 1 has Page 1 of 1 and also Page 1 of 45 on the
same page. Can anyone tell me if this is possible and how I do it?