Printing multiple sheets in an array

[rwong]

Hi -

I'm trying to create a macro that allows me to print certain sheets from a worksheet, whereby the names of the sheets I want to print are contained in a range (ie "printrange")

I'm not very familiar with writing code but I tried to modify an existing macro I had, which cycles through the sheets that I've listed out in the range and prints it one by one (pls see below). What I would like some help on, is to create a macro which effectively selects multiple sheets and issues a print command. This is so that when I use it to print PDF files, I don't get multiple PDF files. I just can't figure out how to get the name of the sheets (which are not always the same - ie may want to print out different pages of output) into an array...

Thanks in advance!


Old macro:


Sub PrintMacro()

Dim PrintRange As Range
Set PrintRange = Range("SheetsToPrint")

confirm = InputBox("Are you sure? (enter 'y' to continue)")

If confirm <> "y" Then GoTo TheEnd

For Each X In PrintRange
If X.Value <> "" Then
Worksheets(X.Value).PrintOut Copies:=1, Collate:=True
End If
Next X

TheEnd:


End Sub

[Norman Jones]

Hi R,

Try something like (untested):

Sub PrintMacro()

Dim PrintRange As Range
Dim rCell As Range
Dim Confirm As Variant
Set PrintRange = Range("SheetsToPrint")

Confirm = InputBox("Are you sure? (enter 'y' to continue)")

If UCase(Confirm) <> "Y" Then GoTo TheEnd

For Each rCell In PrintRange.Cells
If Not IsEmpty(rCell) Then
Sheets(rCell.Value).Select Replace:=False
End If
Next rCell

Selection.PrintOut Copies:=1, Collate:=True

TheEnd:

End Sub

---
Regards,
Norman



"rwong" <[email protected]> wrote in
message news:[email protected]...
>
> Hi -
>
> I'm trying to create a macro that allows me to print certain sheets
> from a worksheet, whereby the names of the sheets I want to print are
> contained in a range (ie "printrange")
>
> I'm not very familiar with writing code but I tried to modify an
> existing macro I had, which cycles through the sheets that I've listed
> out in the range and prints it one by one (pls see below). What I
> would like some help on, is to create a macro which effectively selects
> multiple sheets and issues a print command. This is so that when I use
> it to print PDF files, I don't get multiple PDF files. I just can't
> figure out how to get the name of the sheets (which are not always the
> same - ie may want to print out different pages of output) into an
> array...
>
> Thanks in advance!
>
>
> Old macro:
>
>
> Sub PrintMacro()
>
> Dim PrintRange As Range
> Set PrintRange = Range("SheetsToPrint")
>
> confirm = InputBox("Are you sure? (enter 'y' to continue)")
>
> If confirm <> "y" Then GoTo TheEnd
>
> For Each X In PrintRange
> If X.Value <> "" Then
> Worksheets(X.Value).PrintOut Copies:=1, Collate:=True
> End If
> Next X
>
> TheEnd:
>
>
> End Sub
>
>
> --
> rwong
> ------------------------------------------------------------------------
> rwong's Profile:
> http://www.excelforum.com/member.php...o&userid=12735
> View this thread: http://www.excelforum.com/showthread...hreadid=390812
>

[rwong]

Hi Norman,

Thanks for the super quick reply.

Almost there - it selects the sheets I want in the range - however, the range contains some formulas (=IF(C50=0,"",C50)) - so when it gets there, I get an error "Subscript out of range" - Suspect that its from the "isempty" command?

Also, the macro starts from an "Index" sheet and I think that it selects this sheet by default even though the sheet name is not in the printrange.

[Norman Jones]

Hi R,

Change:

> Selection.PrintOut Copies:=1, Collate:=True
to:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:%[email protected]...
> Hi R,
>
> Try something like (untested):
>
> Sub PrintMacro()
>
> Dim PrintRange As Range
> Dim rCell As Range
> Dim Confirm As Variant
> Set PrintRange = Range("SheetsToPrint")
>
> Confirm = InputBox("Are you sure? (enter 'y' to continue)")
>
> If UCase(Confirm) <> "Y" Then GoTo TheEnd
>
> For Each rCell In PrintRange.Cells
> If Not IsEmpty(rCell) Then
> Sheets(rCell.Value).Select Replace:=False
> End If
> Next rCell
>
> Selection.PrintOut Copies:=1, Collate:=True
>
> TheEnd:
>
> End Sub
>
> ---
> Regards,
> Norman
>
>
>
> "rwong" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi -
>>
>> I'm trying to create a macro that allows me to print certain sheets
>> from a worksheet, whereby the names of the sheets I want to print are
>> contained in a range (ie "printrange")
>>
>> I'm not very familiar with writing code but I tried to modify an
>> existing macro I had, which cycles through the sheets that I've listed
>> out in the range and prints it one by one (pls see below). What I
>> would like some help on, is to create a macro which effectively selects
>> multiple sheets and issues a print command. This is so that when I use
>> it to print PDF files, I don't get multiple PDF files. I just can't
>> figure out how to get the name of the sheets (which are not always the
>> same - ie may want to print out different pages of output) into an
>> array...
>>
>> Thanks in advance!
>>
>>
>> Old macro:
>>
>>
>> Sub PrintMacro()
>>
>> Dim PrintRange As Range
>> Set PrintRange = Range("SheetsToPrint")
>>
>> confirm = InputBox("Are you sure? (enter 'y' to continue)")
>>
>> If confirm <> "y" Then GoTo TheEnd
>>
>> For Each X In PrintRange
>> If X.Value <> "" Then
>> Worksheets(X.Value).PrintOut Copies:=1, Collate:=True
>> End If
>> Next X
>>
>> TheEnd:
>>
>>
>> End Sub
>>
>>
>> --
>> rwong
>> ------------------------------------------------------------------------
>> rwong's Profile:
>> http://www.excelforum.com/member.php...o&userid=12735
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=390812
>>
>
>

[Norman Jones]

Hi R,

Did you change:

> Selection.PrintOut Copies:=1, Collate:=True

to:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

as suggested in my follow up post?

With the former, the sheets will be deselected; with the latter, the sheet
selection should be retained.

Indeed, you might care to add a final line:

Sheets(1).Select

to break the multisheet selection, after printing.

---
Regards,
Norman



"rwong" <[email protected]> wrote in
message news:[email protected]...
>
> Actually - I deleted the pseudo blank cells to test it out further - and
> apparently, right before the print command is issued, the sheets get
> unselected, and only the current highlighted cell gets printed...
>
> Further help would be much appreciated.
>
>
> --
> rwong
> ------------------------------------------------------------------------
> rwong's Profile:
> http://www.excelforum.com/member.php...o&userid=12735
> View this thread: http://www.excelforum.com/showthread...hreadid=390812
>

[rwong]

Hi Norman,

I did indeed make the change as suggested.

Was getting a subscript range error because my cells weren't really empty (ie my sheettoprint range was fill with isblank formula.) -> my solution was to change the isempty function to a isnumeric function (since I could get it to return zero).

The other problem with the starting sheet being selected as part of the print range is still present however. Best if I couild get rid of it - but its something I can live with I suppose.

Thanks alot!

[Norman Jones]

Hi R,

> The other problem with the starting sheet being selected as part of the
> print range is still present however. Best if I couild get rid of it -
> but its something I can live with I suppose.

To obviate this problem, try:

'===========================>>
Sub PrintMacro()

Dim PrintRange As Range
Dim rCell As Range
Dim Confirm As Variant
Dim blFlag As Boolean

blFlag = True

Set PrintRange = Range("SheetsToPrint")

Confirm = InputBox("Are you sure? (enter 'y' to continue)")

If UCase(Confirm) <> "Y" Then GoTo TheEnd

For Each rCell In PrintRange.Cells
If Not IsNumeric(rCell) Then
Sheets(rCell.Value).Select Replace:=blFlag
blFlag = False
End If
Next rCell

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Sheets(1).Select

TheEnd:

End Sub

'<<===========================

---
Regards,
Norman



"rwong" <[email protected]> wrote in message
news:[email protected]...
>
> Hi Norman,
>
> I did indeed make the change as suggested.
>
> Was getting a subscript range error because my cells weren't really
> empty (ie my sheettoprint range was fill with isblank formula.) -> my
> solution was to change the isempty function to a isnumeric function
> (since I could get it to return zero).
>
> The other problem with the starting sheet being selected as part of the
> print range is still present however. Best if I couild get rid of it -
> but its something I can live with I suppose.
>
> Thanks alot!
>
>
> --
> rwong
> ------------------------------------------------------------------------
> rwong's Profile:
> http://www.excelforum.com/member.php...o&userid=12735
> View this thread: http://www.excelforum.com/showthread...hreadid=390812
>

[rwong]

PERFECT!

Thanks sooo much! your kind help is much appreciated...