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(extern al:=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(extern al:=True)
End With
Next I

End Sub

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

Jim Thomlinson


"cass calculator" wrote:

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(extern al:=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(extern al:=True)
End With
Next I

End Sub

On Aug 1, 1:35*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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...
--
HTH...

Jim Thomlinson

"cass calculator" wrote:
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(extern al:=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(extern al:=True)
* * * * End With
* * Next I

End Sub

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. ("A1:D5", "A7:D7"),
but that syntax isn't working the way I've applied it in my initial
post. Can anyone else help me with this?

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

Jim Thomlinson


"cass calculator" wrote:

On Aug 1, 1:35 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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...
--
HTH...

Jim Thomlinson

"cass calculator" wrote:
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(extern al:=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(extern al:=True)
End With
Next I

End Sub

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. ("A1:D5", "A7:D7"),
but that syntax isn't working the way I've applied it in my initial
post. Can anyone else help me with this?

On Aug 1, 1:56*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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
--
HTH...

Jim Thomlinson

"cass calculator" wrote:
On Aug 1, 1:35 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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...
--
HTH...

Jim Thomlinson

"cass calculator" wrote:
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(extern al:=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(extern al:=True)
* * * * End With
* * Next I

End Sub

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. ("A1:D5", "A7:D7"),
but that syntax isn't working the way I've applied it in my initial
post. *Can anyone else help me with this?

Perfect! Thank you very much!