ExcelBanter - Print Selection

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Print Selection (https://www.excelbanter.com/excel-programming/407574-print-selection.html)

MikeF[_2_]

Print Selection

Hello,

The same range needs to be printed for one or sometimes more sheets in a
workbook.
This *should* be easy, but can't seem to make it happen properly.

Can anyone please correct the following code apppropriatlely?
Thanx in advance.
- Mike

Sub PrintDetail()
Range("B7:Y40").Select
For Each Sh In ActiveWindow.SelectedSheets
Sh.PageSetup.PrintTitleRows = "$1:$6"
Sh.Selection.PrintOut Collate:=True
Next
Next
End Sub

Jim Rech[_2_]

Print Selection

Jim Thomlinson

Print Selection

Try this...

Sub PrintDetail()
'Range("B7:Y40").Select
For Each Sh In ActiveWindow.SelectedSheets
Sh.PageSetup.PrintTitleRows = "$1:$6"
Sh.Range("B7:Y40").PrintOut Collate:=True
'Next
Next
End Sub

--
HTH...

Jim Thomlinson

"MikeF" wrote:

Hello,

The same range needs to be printed for one or sometimes more sheets in a
workbook.
This *should* be easy, but can't seem to make it happen properly.

Can anyone please correct the following code apppropriatlely?
Thanx in advance.
- Mike

Sub PrintDetail()
Range("B7:Y40").Select
For Each Sh In ActiveWindow.SelectedSheets
Sh.PageSetup.PrintTitleRows = "$1:$6"
Sh.Selection.PrintOut Collate:=True
Next
Next
End Sub

MikeF[_2_]

Print Selection

Jim,

Thank you, this works.

Except [asking for the world here <g] --- It prints each sheet separately.
Example - when printing three sheets, the footer on each says Page 1 of 1,
as opposed to Page 1 of 3/etc.
Is there any way to fix that?

- Mike

"Jim Rech" wrote:

Sub PrintEm()
Dim WS As Worksheet
For Each WS In ActiveWindow.SelectedSheets
WS.Range("B7:Y40").PrintOut
Next
End Sub

--
Jim
"MikeF" wrote in message
...
|
| Hello,
|
| The same range needs to be printed for one or sometimes more sheets in a
| workbook.
| This *should* be easy, but can't seem to make it happen properly.
|
| Can anyone please correct the following code apppropriatlely?
| Thanx in advance.
| - Mike
|
|
| Sub PrintDetail()
| Range("B7:Y40").Select
| For Each Sh In ActiveWindow.SelectedSheets
| Sh.PageSetup.PrintTitleRows = "$1:$6"
| Sh.Selection.PrintOut Collate:=True
| Next
| Next
| End Sub
|

MikeF[_2_]

Print Selection

Jim,

Thank you, this works.

Except [asking for the world here <g] --- It prints each sheet separately.
Example - when printing three sheets, the footer on each says Page 1 of 1,
as opposed to Page 1 of 3/etc.
Is there any way to fix that?

- Mike

"Jim Thomlinson" wrote:

Try this...

Sub PrintDetail()
'Range("B7:Y40").Select
For Each Sh In ActiveWindow.SelectedSheets
Sh.PageSetup.PrintTitleRows = "$1:$6"
Sh.Range("B7:Y40").PrintOut Collate:=True
'Next
Next
End Sub

--
HTH...

Jim Thomlinson

"MikeF" wrote:

Hello,

The same range needs to be printed for one or sometimes more sheets in a
workbook.
This *should* be easy, but can't seem to make it happen properly.

Can anyone please correct the following code apppropriatlely?
Thanx in advance.
- Mike

Sub PrintDetail()
Range("B7:Y40").Select
For Each Sh In ActiveWindow.SelectedSheets
Sh.PageSetup.PrintTitleRows = "$1:$6"
Sh.Selection.PrintOut Collate:=True
Next
Next
End Sub

Jim Rech[_2_]

Print Selection

MikeF[_2_]

Print Selection

Jim,
Thanx, but it stalls.
Doesn't seem to like the "name" syntax is the best I can assess.
- Mike

"Jim Rech" wrote:

Good point. But the problem is - to print them as one job they have to have
"PrintArea" defined on all sheets. Easy to do with a macro but you have to
remember that it's set and will affect manual prints of these sheets. Of
course you can expand the macro to reset the print area.<g

Sub PrintEm()
Dim WS As Worksheet
For Each WS In ActiveWindow.SelectedSheets
WS.Range("B7:Y40").Name = WS.Name & "!PrintArea"
Next
ActiveWindow.SelectedSheets.PrintOut
End Sub

--
Jim
"MikeF" wrote in message
...
| Jim,
|
| Thank you, this works.
|
| Except [asking for the world here <g] --- It prints each sheet
separately.
| Example - when printing three sheets, the footer on each says Page 1 of 1,
| as opposed to Page 1 of 3/etc.
| Is there any way to fix that?
|
| - Mike
|
| "Jim Rech" wrote:
|
| Sub PrintEm()
| Dim WS As Worksheet
| For Each WS In ActiveWindow.SelectedSheets
| WS.Range("B7:Y40").PrintOut
| Next
| End Sub
|
|
| --
| Jim
| "MikeF" wrote in message
| ...
| |
| | Hello,
| |
| | The same range needs to be printed for one or sometimes more sheets in
a
| | workbook.
| | This *should* be easy, but can't seem to make it happen properly.
| |
| | Can anyone please correct the following code apppropriatlely?
| | Thanx in advance.
| | - Mike
| |
| |
| | Sub PrintDetail()
| | Range("B7:Y40").Select
| | For Each Sh In ActiveWindow.SelectedSheets
| | Sh.PageSetup.PrintTitleRows = "$1:$6"
| | Sh.Selection.PrintOut Collate:=True
| | Next
| | Next
| | End Sub
| |
|
|
|

Jim Rech[_2_]

Print Selection

Dave Peterson

Print Selection

Sometimes, you have to surround the worksheet name with apostrophes.

I'd try:

WS.Range("B7:Y40").Name = "'" & WS.Name & "'" & "!PrintArea"
or combining the last two strings into one:
WS.Range("B7:Y40").Name = "'" & WS.Name & "'!PrintArea"

MikeF wrote:

Jim,
Thanx, but it stalls.
Doesn't seem to like the "name" syntax is the best I can assess.
- Mike

"Jim Rech" wrote:

Good point. But the problem is - to print them as one job they have to have
"PrintArea" defined on all sheets. Easy to do with a macro but you have to
remember that it's set and will affect manual prints of these sheets. Of
course you can expand the macro to reset the print area.<g

Sub PrintEm()
Dim WS As Worksheet
For Each WS In ActiveWindow.SelectedSheets
WS.Range("B7:Y40").Name = WS.Name & "!PrintArea"
Next
ActiveWindow.SelectedSheets.PrintOut
End Sub

--
Jim
"MikeF" wrote in message
...
| Jim,
|
| Thank you, this works.
|
| Except [asking for the world here <g] --- It prints each sheet
separately.
| Example - when printing three sheets, the footer on each says Page 1 of 1,
| as opposed to Page 1 of 3/etc.
| Is there any way to fix that?
|
| - Mike
|
| "Jim Rech" wrote:
|
| Sub PrintEm()
| Dim WS As Worksheet
| For Each WS In ActiveWindow.SelectedSheets
| WS.Range("B7:Y40").PrintOut
| Next
| End Sub
|
|
| --
| Jim
| "MikeF" wrote in message
| ...
| |
| | Hello,
| |
| | The same range needs to be printed for one or sometimes more sheets in
a
| | workbook.
| | This *should* be easy, but can't seem to make it happen properly.
| |
| | Can anyone please correct the following code apppropriatlely?
| | Thanx in advance.
| | - Mike
| |
| |
| | Sub PrintDetail()
| | Range("B7:Y40").Select
| | For Each Sh In ActiveWindow.SelectedSheets
| | Sh.PageSetup.PrintTitleRows = "$1:$6"
| | Sh.Selection.PrintOut Collate:=True
| | Next
| | Next
| | End Sub
| |
|
|
|

--

Dave Peterson

MikeF[_2_]

Print Selection

Dave,
Thanx, this worked.
Except [<g] --- The print range is moot --- sub-routine considers only the
pre-defined print area for each sheet.
That having been said ... is there anyway to pre-define each sheet's print
area - *all of them the same!* - in the first part of the code?

- Mike

"Dave Peterson" wrote:

Sometimes, you have to surround the worksheet name with apostrophes.

I'd try:

WS.Range("B7:Y40").Name = "'" & WS.Name & "'" & "!PrintArea"
or combining the last two strings into one:
WS.Range("B7:Y40").Name = "'" & WS.Name & "'!PrintArea"

MikeF wrote:

Jim,
Thanx, but it stalls.
Doesn't seem to like the "name" syntax is the best I can assess.
- Mike

"Jim Rech" wrote:

Good point. But the problem is - to print them as one job they have to have
"PrintArea" defined on all sheets. Easy to do with a macro but you have to
remember that it's set and will affect manual prints of these sheets. Of
course you can expand the macro to reset the print area.<g

Sub PrintEm()
Dim WS As Worksheet
For Each WS In ActiveWindow.SelectedSheets
WS.Range("B7:Y40").Name = WS.Name & "!PrintArea"
Next
ActiveWindow.SelectedSheets.PrintOut
End Sub

--
Jim
"MikeF" wrote in message
...
| Jim,
|
| Thank you, this works.
|
| Except [asking for the world here <g] --- It prints each sheet
separately.
| Example - when printing three sheets, the footer on each says Page 1 of 1,
| as opposed to Page 1 of 3/etc.
| Is there any way to fix that?
|
| - Mike
|
| "Jim Rech" wrote:
|
| Sub PrintEm()
| Dim WS As Worksheet
| For Each WS In ActiveWindow.SelectedSheets
| WS.Range("B7:Y40").PrintOut
| Next
| End Sub
|
|
| --
| Jim
| "MikeF" wrote in message
| ...
| |
| | Hello,
| |
| | The same range needs to be printed for one or sometimes more sheets in
a
| | workbook.
| | This *should* be easy, but can't seem to make it happen properly.
| |
| | Can anyone please correct the following code apppropriatlely?
| | Thanx in advance.
| | - Mike
| |
| |
| | Sub PrintDetail()
| | Range("B7:Y40").Select
| | For Each Sh In ActiveWindow.SelectedSheets
| | Sh.PageSetup.PrintTitleRows = "$1:$6"
| | Sh.Selection.PrintOut Collate:=True
| | Next
| | Next
| | End Sub
| |
|
|
|

--

Dave Peterson

Dave Peterson

Print Selection

WS.Range("B7:Y40").Name = "'" & WS.Name & "'" & "!Print_Area"

I didn't notice the typo.

MikeF wrote:

Dave,
Thanx, this worked.
Except [<g] --- The print range is moot --- sub-routine considers only the
pre-defined print area for each sheet.
That having been said ... is there anyway to pre-define each sheet's print
area - *all of them the same!* - in the first part of the code?

- Mike

"Dave Peterson" wrote:

Sometimes, you have to surround the worksheet name with apostrophes.

I'd try:

WS.Range("B7:Y40").Name = "'" & WS.Name & "'" & "!PrintArea"
or combining the last two strings into one:
WS.Range("B7:Y40").Name = "'" & WS.Name & "'!PrintArea"

MikeF wrote:

Jim,
Thanx, but it stalls.
Doesn't seem to like the "name" syntax is the best I can assess.
- Mike

"Jim Rech" wrote:

Good point. But the problem is - to print them as one job they have to have
"PrintArea" defined on all sheets. Easy to do with a macro but you have to
remember that it's set and will affect manual prints of these sheets. Of
course you can expand the macro to reset the print area.<g

Sub PrintEm()
Dim WS As Worksheet
For Each WS In ActiveWindow.SelectedSheets
WS.Range("B7:Y40").Name = WS.Name & "!PrintArea"
Next
ActiveWindow.SelectedSheets.PrintOut
End Sub

--
Jim
"MikeF" wrote in message
...
| Jim,
|
| Thank you, this works.
|
| Except [asking for the world here <g] --- It prints each sheet
separately.
| Example - when printing three sheets, the footer on each says Page 1 of 1,
| as opposed to Page 1 of 3/etc.
| Is there any way to fix that?
|
| - Mike
|
| "Jim Rech" wrote:
|
| Sub PrintEm()
| Dim WS As Worksheet
| For Each WS In ActiveWindow.SelectedSheets
| WS.Range("B7:Y40").PrintOut
| Next
| End Sub
|
|
| --
| Jim
| "MikeF" wrote in message
| ...
| |
| | Hello,
| |
| | The same range needs to be printed for one or sometimes more sheets in
a
| | workbook.
| | This *should* be easy, but can't seem to make it happen properly.
| |
| | Can anyone please correct the following code apppropriatlely?
| | Thanx in advance.
| | - Mike
| |
| |
| | Sub PrintDetail()
| | Range("B7:Y40").Select
| | For Each Sh In ActiveWindow.SelectedSheets
| | Sh.PageSetup.PrintTitleRows = "$1:$6"
| | Sh.Selection.PrintOut Collate:=True
| | Next
| | Next
| | End Sub
| |
|
|
|

--

Dave Peterson

--

Dave Peterson

MikeF[_2_]

Print Selection

Dave,
Juggled a few things on my own, and as follows is the code that works for
what I need.
Thanx for all your assistance.
- Mike

Sub PrintDetail()
Dim WS As Worksheet
For Each WS In ActiveWindow.SelectedSheets
WS.PageSetup.PrintArea = ("B7:i22")
WS.PageSetup.PrintTitleRows = "$1:$6"
Next
ActiveWindow.SelectedSheets.PrintOut
End Sub

"Dave Peterson" wrote:

WS.Range("B7:Y40").Name = "'" & WS.Name & "'" & "!Print_Area"

I didn't notice the typo.

MikeF wrote:

Dave,
Thanx, this worked.
Except [<g] --- The print range is moot --- sub-routine considers only the
pre-defined print area for each sheet.
That having been said ... is there anyway to pre-define each sheet's print
area - *all of them the same!* - in the first part of the code?

- Mike

"Dave Peterson" wrote:

Sometimes, you have to surround the worksheet name with apostrophes.

I'd try:

WS.Range("B7:Y40").Name = "'" & WS.Name & "'" & "!PrintArea"
or combining the last two strings into one:
WS.Range("B7:Y40").Name = "'" & WS.Name & "'!PrintArea"

MikeF wrote:

Jim,
Thanx, but it stalls.
Doesn't seem to like the "name" syntax is the best I can assess.
- Mike

"Jim Rech" wrote:

Good point. But the problem is - to print them as one job they have to have
"PrintArea" defined on all sheets. Easy to do with a macro but you have to
remember that it's set and will affect manual prints of these sheets. Of
course you can expand the macro to reset the print area.<g

Sub PrintEm()
Dim WS As Worksheet
For Each WS In ActiveWindow.SelectedSheets
WS.Range("B7:Y40").Name = WS.Name & "!PrintArea"
Next
ActiveWindow.SelectedSheets.PrintOut
End Sub

--
Jim
"MikeF" wrote in message
...
| Jim,
|
| Thank you, this works.
|
| Except [asking for the world here <g] --- It prints each sheet
separately.
| Example - when printing three sheets, the footer on each says Page 1 of 1,
| as opposed to Page 1 of 3/etc.
| Is there any way to fix that?
|
| - Mike
|
| "Jim Rech" wrote:
|
| Sub PrintEm()
| Dim WS As Worksheet
| For Each WS In ActiveWindow.SelectedSheets
| WS.Range("B7:Y40").PrintOut
| Next
| End Sub
|
|
| --
| Jim
| "MikeF" wrote in message
| ...
| |
| | Hello,
| |
| | The same range needs to be printed for one or sometimes more sheets in
a
| | workbook.
| | This *should* be easy, but can't seem to make it happen properly.
| |
| | Can anyone please correct the following code apppropriatlely?
| | Thanx in advance.
| | - Mike
| |
| |
| | Sub PrintDetail()
| | Range("B7:Y40").Select
| | For Each Sh In ActiveWindow.SelectedSheets
| | Sh.PageSetup.PrintTitleRows = "$1:$6"
| | Sh.Selection.PrintOut Collate:=True
| | Next
| | Next
| | End Sub
| |
|
|
|

--

Dave Peterson

--

Dave Peterson

MikeF[_2_]

Print Selection

MikeF[_2_]

Print Selection

All times are GMT +1. The time now is 01:15 PM.