Selecting & Printing Ranges from Multiple Worksheets 1

[bstanton]

I would like to write code that selects certain ranges from different worksheets within the same workbook and then print that total selection to file or a printer depending on what the user decides. These ranges would be predetermined based on user input. For instance, if option one is selected, then the selection I would like to print would be; sheet1(A1:E20), sheet2(B2:G16), sheet3(A6:G22), Chart1, Chart2,…. you get the idea. If option two is selected, then the selection I would like to print would be; sheet1(A21:E40), sheet2(B17:G26), sheet3(A23:G41), Chart1, Chart2, Chart3, Chart4….. I want the code to print the range selected on sheet1 on the first page of the output and the range selected on sheet2 be printed on the second page and so on.
I believe I have the print part of the code figured out, but I cannot quite figure out how to create this multi sheet range. Right, wrong or indifferent I have tried playing around with unions, but have had no success. Any guidance would be greatly appreciated. Thanks.

 

[Loomah]

how are you selectiong the 'option'? i'd assume option buttons.

essentially you need to create an array of what is to be printed. can't recall the exact method and i'm not connected to any printers at the moment but the easiest solution is to record yourself printing each option.

you can then strip away all the printer information such as margins and orientation etc and just keep the 'what to print' bit.

depending on the option assign the 'what to print' bit to a variable and use that variable in the print code. this means that you only need the print setup bits of code once and assign the 'what to print' depending on the option chosen.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bstanton]

Loomah,

Thanks for your input. You are correct, the user would select an option button and depending on which option button they selected, a multiple range selection across several worksheets would be selected. All of the ideas I have come up with on how I might achieve this operation have failed. I have tried the union method (defining separate ranges and then joining them together in a union), but I get a run time error (1004). Now, if I take that same code and modify it to select different ranges on the same worksheet it works just fine. Another method I have tried was defining an array of several worksheet ranges, but that gives me a subscript out of range error.
The overall goal is to have all of these selections print into a single pdf document.

 

[SkipVought]

You cannot SELECT objects on multiple sheets. SELECT only works on the ActiveSheet.

If you need to print the WORKBOOK in one fell swoop, you might try HIDING rows & columns that are outside the print range on each sheet, before issuing a PRINT WORKBOOK type process.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Loomah]

hi
again i haven't actually tested this fully due to office layout (printer's as far away from me as is possible!)

should give some better indication of the way i was thinking on friday (while lying around doing nothing!)

Sub stance()
Dim shts
Dim printa
Dim i As Integer
'=============================================================
'   set these 2 variables depending on the options selection
shts = Array("sheet1", "sheet4", "chart1")
'   printa needs to match order of sheets in shts
printa = Array("$A$12:$H$29", "$B$1:$B$29")
'=============================================================
    For i = LBound(shts) To UBound(shts)
    
        With Sheets(shts(i))
            If .Type <> 3 Then 'for some reason the constant "xlChart" isn't working for me
                With .PageSetup
                    .PrintArea = printa(i)
                    'other print setup stuff
                End With
            Else
                With .PageSetup
                    'print setup stuff here
                End With
            End If
            '.PrintOut copies:=1
        End With
    Next

End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

I want the code to print the range selected on sheet1 on the first page of the output and the range selected on sheet2 be printed on the second page and so on.

This requirement means that you cannot PrintOut in a loop. It means that you must PrintOut the WORKBOOK.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Loomah]

skip
lost me there!

sheets(shts).printout

would work without printing the whole book and is probably better. would need to ensure that page setup is complete for each sheet especially if page numbers are required (something that isn't really possible in my original code)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

The PrintOut/PrintPreview methods refer to range too, so you can simply:
[tt]Worksheets("sheet1").Range("A1:E20").PrintOut[/tt]

combo

 

[SkipVought]

The point is that I think that the OP wants to print the entire workbook, with selected ranges, in order to have the range on sheet 1 as page 1, the range on sheet 2 as page 2, etc.

Doing separate prints in a loop, results in sheet 1 as page 1, sheet 2 as page 1, etc.

But maybe my perception is incorrect.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Loomah]

the loop is more to do with setting the print range which will change depending on the option regardless of whether some or all of the sheets need to be printed.

i've simply worked on the basis that the op is for different ranges and sheets.

i too may be wrong!, but i think all options have been covered!!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​