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

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,

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
|

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,
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
| |
|
|
|

Runs here. Check for a typo.

--
Jim
"MikeF" wrote in message
...
|
| 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
| | |
| |
| |
| |
|
|
|

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

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,

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,
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


"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