Formatting Page Layouts for Multiple Sheets (Excel 2003)

[Arcturus16a]

Accounting software that we use creates an Excel file with multiple tabs and print settings including Headers and Footers. When I tried to modify the settings to change the Left Margin I held the CTRL key so that all tabbed sheets would receive the same change. Unfortunately, that also changes all of the other settings in page layout. That's not what I wanted since the app that created the Excel file put a unique ID for each tabbed sheet in the Page Header.

I would like a macro to modify print setting across multiple sheets, but only specific page layout options. If I record a macro with all of the setting I would like to use, I get something like this:

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.RightFooter = "&9Page &P of &N"
End With
End Sub

I can add all of the other settings I would like modified like Margins, Orientation, Zoom etc.

BUT, I do not know how to cycle the macro through the workbook so that each sheet is updated.

[sdckapr]

Something like this (beware Air code...)

dim sht as variant
for each sht in activeworkbook.sheets
with sht.pagesetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintArea = ""
.RightFooter = "&9Page &P of &N"
End With
next


Steve

[pieterse]

You may benefit from this little contraption I built some years ago:
http://www.jkp-ads.com/download.asp#headerfooter