Results 1 to 10 of 10
  1. #1
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Set Print Area Across Multiple Sheets

    Excel 97 does not have the capability to select a print area across multiple sheets, unless I've seriously overlooked something. Try it for your self, select multiple sheets in a workbook, select a range (larger than one cell) within all those sheets; you will find that the File, Page Setup, Sheet, Print Area dialog is grayed out (if you use the Set Print Area icon, it's grayed out).[img]/w3timages/icons/nuts.gif[/img]

    I frequently have workbooks with multiple sheets with an identical layout, and would like to select the print area on all of them with one keypress or click. But I'd like this to be specific to only the selected sheets, as within such a workbook I often have source data or other sheets with a different print layout. I don't have the skill to write code for this.[img]/w3timages/icons/crybaby.gif[/img] Call me the VBA mendicant (some say miscreant).

    Can someone direct me to a solution, or provide a macro to answer my prayers? Is this resolved in Excel2000?
    -John ... I float in liquid gardens
    UTC -7ąDS

  2. The Following User Says Thank You to JohnBF For This Useful Post:

    Andrew Cronnolly (2012-01-03)

  3. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set Print Area Across Multiple Sheets

    I'm not sure I understand you problem but think you would like to set up identical sheets with the same Page Settings, including Print Area.

    I dont know about one keypress solutions, but a quick way I use is to set one of the sheets the way I want it and immediately that is done select what ever other sheets I need and press F4. In fact you can just go trough the motions of page SetUp and then repeat (F4) for any sheet you want to have the same Page SetUp

    regards,

    Andrew

  4. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Set Print Area Across Multiple Sheets

    Sounds like you understand the problem, and your solution is almost what I'm doing. (Try not to laugh too much.) I usually select the range throughout the selected sheets. Then I have to deselect the sheets and go to the first one and click the SetPrintArea icon. (All the target sheets retain the selected range.) Then, right hand on mouse, pointer over the SetPrintArea icon, left hand on the right side of the keyboard, I Ctrl-PageDn through the required sheets, while the right hand clicks the icon each time.

    I do one like this about every fortnight and I'm just sick of doing it 43 times for my usual 43 market areas! Carpal Tunnel Syndrome!
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set Print Area Across Multiple Sheets

    Just to make sure you are not doing too much clicking.

    If You have a work sheet with Page Setup the way you want, all you need do is select that sheet, goto File | Page Setup and just click on any setting without changing it. Then seclect OK.

    Then click on the tabs of the other sheets with the same setup and press F4. There is no need to select ranges. Hope that makes it easier for you.

    That works in XL 97 and 2000

    Andrew C

  6. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Print Area Across Multiple Sheets

    The following VBA routine will set the print area for worksheet Sheet1 to the range $A$1:$C$5. You just need to add similar statements for all of the sheets for which you want to set the print area. I don't see any way to have a routing identify the selected sheets and do it for them. You could pick up the selection from the active sheet to determine what range to set.

    <pre>Public Sub SetPrintArea()
    Worksheets("Sheet1").PageSetup.PrintArea = "$A$1:$C$5"
    End Sub
    </pre>

    Legare Coleman

  7. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Print Area Across Multiple Sheets

    how about something along the lines of:

    For Each sh In Workbooks("BOOK1.XLS").Windows(1).SelectedSheets
    sh.PageSetup.PrintArea = "$A$1:$C$5"
    Next

    Would that work?

    Brooke

  8. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Print Area Across Multiple Sheets

    Thanks to Bill Manville in the CompuServe Excel forum, I have found the collection of selected sheets. The following VBA routine will set the print area on all selected sheets to the current selection.

    <pre>Public Sub SetPrintAreas()
    Dim oSheet As Worksheet
    Dim sPrintRange As String
    sPrintRange = Selection.Address
    For Each oSheet In ActiveWindow.SelectedSheets
    With oSheet.PageSetup
    .PrintArea = sPrintRange
    End With
    Next oSheet
    End Sub
    </pre>

    Legare Coleman

  9. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Print Area Across Multiple Sheets

    Yes, thanks to Bill Manville over on CompuServe I found the SelectedSheets collection and have posted a macro in another reply in this thread.
    Legare Coleman

  10. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Set Print Area Across Multiple Sheets

    Legare, this is wonderful. My thanks to you and Bill. Since it will work on both multiple AND single sheets, it gets to attached to the regular "SetPrintArea" button.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Print Area Across Multiple Sheets

    John,

    This is a little late, but it works in XL2K. Select all the identical sheets you want to print, then select the cells you want to print on the first sheet. Press Ctrl-P or do File | Print and click the "Print Selection" option button. Print Preview then shows that all the selected sheets will print just that selected area. No need to set a print area at all.

    kjk

Posting Permissions

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