Selective Page Footers

[Paul Whitaker]

I have a workbook with 10 worksheets where the 5th & 6th worksheets prints onto several pages. I would like to print the entire workbook with the 5th & 6th worksheets only having footers of Page x of y, etc (the number of pages may vary month by month).
When I enter the footers the 5th sheet prints "Page 6 of x" as it is considering the Total number of pages both before and after, and not just the number of pages in the worksheet.
Is there a non-VB way around this.

Thank you

 

[GS2000]

There is an easy way to do this if you are prepared to print out each Worksheet 5 and 6 separately. Select the required sheet and go to File>PageSetup, select the Page tab. Set the "First Page Number” to 1. Do the same for Worksheet 6.
Note this only works if you print the worksheets independently.

I guess you want to be able to print the whole Workbook at once, for this I have a VBA solution I’m afraid. (But it does work and automate the whole process, in XL2000 at least)

PS - if VB is a problem post back and I'll expand on how to get it into the right place.

Paste this into the Workbook panel and it will run each time you print or print preview.

' do this every time before printing
Private Sub Workbook_BeforePrint(Cancel As Boolean)
 Worksheets(5).Activate
 SetFooters
 Worksheets(6).Activate
 SetFooters
End Sub

Public Sub SetFooters()

Dim SheetPgCnt As Integer


'get total pages on this worksheet

SheetPgCnt = ExecuteExcel4Macro("Get.Document(50)")

'Set first page of sheet to 1
With ActiveSheet.PageSetup
        .FirstPageNumber = 1
    End With
'Set details on sheet centre footer
ActiveSheet.PageSetup.CenterFooter = "&P" & " of " & SheetPgCnt

End Sub

This is a first cut and the code could easily be made more efficient and customised to do other things....

GS