Results 1 to 10 of 10
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Print Multiple Ranges (Excel 2002 SP2)

    I have a spreadsheet with about 10 named ranges - each named range covers 1 print worthy page. When I want to print the 10 pages I use a macro that looks like this ...

    <pre>Application.Goto Reference:="Range_1"
    Selection.PrintOut Copies:=1
    Application.Goto Reference:="Range_2"
    Selection.PrintOut Copies:=1
    Application.Goto Reference:="Range_3"
    Selection.PrintOut Copies:=1
    Application.Goto Reference:="Range_4"
    Selection.PrintOut Copies:=1
    etc
    </pre>

    This works fine but each page comes out 1 at a time - a bit of a problem if you share a print (or worse still, have banners turned on). I was trying to come up with a way of selecting all the range names at once and then printing them. I got as far as ...

    <pre>range(array("Range_1","Range_2","Range_3","Ra nge_4")).select
    Selection.PrintOut Copies:=1
    </pre>

    ... but that doesn't want to work. Anyone got any suggestions?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  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: Print Multiple Ranges (Excel 2002 SP2)

    See the suggestions in reply to <post#= 334408>post 334408</post#>

    Steve

  3. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Multiple Ranges (Excel 2002 SP2)

    I use the camera trick and the print selection trick often. However, this is slightly different. Each range represents 1 descrete page of information. I cannot use the camera option as the size of the ranges change, they would form a very long collection and (I think) I would have to manually put page breaks in.

    I've used something similar when selecting a whole bunch of sheets to print - it appears that sheets(array(...)).select, selection.print works. However these ranges are spread over a mixture of the same sheet and other sheets.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  4. #4
    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: Print Multiple Ranges (Excel 2002 SP2)

    The camera tool will resize if you reference a name and the name is "dynamic" (expands and contracts based on formulae, usually using OFFSET). You have another limitation with camera tool in the size that is allowed.

    You can select multiple sheets to print (with their own print ranges) and they will print as if they are 1 item (numbered 1 - end, all continuous).

    If you have multiple ranges, you can move them to other sheets to print (camera tool or cell reference).

    Another "indirect" way, is to use a macro to change the print range and also keep track of the "current running" page number and change the header/footer via code to put your "running" page number on the sheets.

    Steve

  5. #5
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Multiple Ranges (Excel 2002 SP2)

    Have you tried the Report Manager?

    "Using the Report Manager add-in program, you can combine worksheets, views, and scenarios into printable reports. For example, if you have a Best Case and a Worst case scenario, a Summary view, and a Details view, you can create a report that presents the Best Case scenario in the Details view and another report that presents the Best Case Scenario in the Summary view. Reports that you create are automatically saved with your workbook so that you can print them at any time.
    The Excel 2002 Report Manager add-in is not included with Excel 2002, as was the case with previous versions of Excel. To use it, you must first download the Excel 2002 Report Manager add-in from Downloads on Microsoft Office Online. Follow the instructions on the Downloads page to install the Report Manager."
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

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

    Re: Print Multiple Ranges (Excel 2002 SP2)

    Define another name, lets say called AllRanges. Use this refersto formula:

    =Range_1,Range_2,Range_3,Range_4,Range_5,Range_6

    Now enter this into the Print_Area defined name:

    =AllRanges
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    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: Print Multiple Ranges (Excel 2002 SP2)

    Jan,
    No matter how long I work with excel, there always seems to be new stuff to learn! I never knew you could do this. Tim should thus be able to add the code:

    <pre> Dim rToPrint As Range
    Dim x As Integer
    Set rToPrint = Range("range_1")
    For x = 2 To 4
    Set rToPrint = Union(rToPrint, Range("range_" & x))
    Next
    rToPrint.PrintOut Copies:=1</pre>

    to his macro and do what he originally asked about.

    Note: the ranges for the macro must all be in the same sheet, and I am pretty sure that all the ranges must be in the same sheet to define a name manually.

    Steve

  8. #8
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Multiple Ranges (Excel 2002 SP2)

    Brilliant. And I came across the union command while I was trawling thru the help system the other day. Talk about not seeing the woods for the trees (or is it the trees for the wood - that saying has always confused me.

    Ok, now let me just summarise what we have found.

    1. Use the union command to build a multi-range range and print that range (only works if all ranges are on one sheet)
    2. Select multiple sheets and then print the collection (only prints the print range on each sheet)

    So, what happens in situations where I have multiple ranges that are on separate sheets (say 5 ranges spread over 3 sheets). I'm going to try to combine the two items above.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  9. #9
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Print Multiple Ranges (Excel 2002 SP2)

    Rats - didn't work. Printarea wants a A1 style reference.

    What I am really trying to do is print out the required pages from a workbook (some areas on one sheet, some areas on separate sheets) to a pdf file. To do this, I need to print all the pages in one go (don't I?). Guess I'll have to create a couple of pdf files.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  10. #10
    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: Print Multiple Ranges (Excel 2002 SP2)

    On each sheet create a separate multi-range rangename. Define the print area for each sheet as its name. This will print each "range" on a separate sheet, but be one print job.

    Now select all the sheets and print. Each sheet will print its multi-range on separate sheets and all will go as one print job.

    Steve

Posting Permissions

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