Print Multiple Ranges (Excel 2002 SP2)

Topic

New Reply

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 …

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

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 …

range(array("Range_1","Range_2","Range_3","Range_4")).select
Selection.PrintOut Copies:=1

… but that doesn’t want to work. Anyone got any suggestions?

Reply | Quote

Replies

See the suggestions in reply to post 334408

Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.”

Reply | Quote

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.”

Reply | Quote

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

Reply | Quote

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:

    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

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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:

    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

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

Reply | Quote

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

Reply | Quote