Print Non-Contiguous Ranges From User Input on All Worksheets

[cass calculator]

I have about 10 different worksheets all with the same number of rows
and columns of data and each worksheet is a different scenario of
data.

I want to be able to have the user select 2 non-contiguous ranges in
an input box that will set the print area for all worksheets in the
workbook. the first range I'm calling ISRng and the second range im
calling BSRng. I'm having two problems:

1) I can only do one contiguous print range at a time.

I know the syntax .PrintArea =
ISRng.Address(external:=True),BSRng.Address(external:=True) is not
correct, but I'm not sure what the correct syntax to define the print
area as two non-contiguous ranges

2) I cannot get this to work for all worksheets in the workbook. I
want the user to be able to select the ranges from whatever the active
sheet is, and have those non-contiguous ranges apply to all sheets in
the workbook

Can someone please help?

Thanks,

Joshua

Sub PrintMacro()
Dim WS_Count As Integer
Dim I As Integer
Dim ISRng As Range
Dim BSRng As Range
On Error Resume Next

Set ISRng = Application.InputBox _
(prompt:="Specify a range:", Type:=8)
If ISRng Is Nothing Then Exit Sub
MsgBox "You selected the following range for the income statement"
& ISRng.Address

Set BSRng = Application.InputBox _
(prompt:="Specify a range:", Type:=8)
If BSRng Is Nothing Then Exit Sub
MsgBox "You selected the following range for the income statement"
& BSRng.Address

WS_Count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_Count
Worksheets(I).Activate
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintArea =
ISRng.Address(external:=True),BSRng.Address(external:=True)
End With
Next I

End Sub

 

[Jim Thomlinson]

XL does not allow for the printing of non-contiguious ranges so you need to
rethink your solution.

One possibility is to hide all of the stuff you do not want to print and
then just print the sheet... Not sure if that will work or you.

Another option is to create a printing worksheet. Copy the selected data
from a scenario sheet to the printing sheet. Print the sheet. Delete all of
the cells on the printing sheet and then go on to the next sheet...

Both are quite doable via macro...

 

[cass calculator]

XL does not allow for the printing of non-contiguious ranges so you need to
rethink your solution.

One possibility is to hide all of the stuff you do not want to print and
then just print the sheet... Not sure if that will work or you.

Another option is to create a printing worksheet. Copy the selected data
from a scenario sheet to the printing sheet. Print the sheet. Delete all of
the cells on the printing sheet and then go on to the next sheet...

Both are quite doable via macro...

I set non-contiguous print ranges regularly in excel by right clicking
and selecting "add to print area", so I know its possible. This
creates multiple print areas that are not connected. When I record
this, the code is just seperated by a comma - i.e. ("A15", "A77"),
but that syntax isn't working the way I've applied it in my initial
post. Can anyone else help me with this?

 

[Jim Thomlinson]

Sorry I thought you wanted this all on one sheet. So long as you are ok with
2 sheets then...

Sub test()
Dim wks As Worksheet
Dim ISRng As Range
Dim BSRng As Range
'On Error Resume Next

Set ISRng = Application.InputBox _
(prompt:="Specify a range:", Type:=8)
If ISRng Is Nothing Then Exit Sub
MsgBox "You selected the following range for the income statement" &
ISRng.Address

Set BSRng = Application.InputBox _
(prompt:="Specify a range:", Type:=8)
If BSRng Is Nothing Then Exit Sub
MsgBox "You selected the following range for the balance sheet" &
BSRng.Address

For Each wks In ThisWorkbook.Worksheets
wks.PageSetup.PrintArea = ISRng.Address & ", " & BSRng.Address
Next wks

End Sub

 

[cass calculator]

Sorry I thought you wanted this all on one sheet. So long as you are ok with
2 sheets then...

Sub test()
    Dim wks As Worksheet
    Dim ISRng As Range
    Dim BSRng As Range
    'On Error Resume Next

    Set ISRng = Application.InputBox _
        (prompt:="Specify a range:", Type:=8)
    If ISRng Is Nothing Then Exit Sub
    MsgBox "You selected the following range for the income statement" &
ISRng.Address

    Set BSRng = Application.InputBox _
        (prompt:="Specify a range:", Type:=8)
    If BSRng Is Nothing Then Exit Sub
    MsgBox "You selected the following range for the balance sheet" &
BSRng.Address

    For Each wks In ThisWorkbook.Worksheets
       wks.PageSetup.PrintArea = ISRng.Address & ", " & BSRng.Address
    Next wks

End Sub

Perfect! Thank you very much!