Selective Page Footers

Paul Whitaker

New Member
Joined
Jul 10, 2002
Messages
19
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 (y)
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

GS2000

New Member
Joined
Dec 26, 2002
Messages
20
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.

Code:
' 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 o_O and the code could easily be made more efficient and customised to do other things....

GS :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top