Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Formatting Page Layouts for Multiple Sheets (Excel 2003)

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formatting Page Layouts for Multiple Sheets (Excel 2003)

    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

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Page Layouts for Multiple Sheets (Excel 2003)

    You may benefit from this little contraption I built some years ago:
    http://www.jkp-ads.com/download.asp#headerfooter
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •