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

 

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

 

[rwong]

Actually - I deleted the pseudo blank cells to test it out further - an
apparently, right before the print command is issued, the sheets ge
unselected, and only the current highlighted cell gets printed...

Further help would be much appreciated

 

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

 

[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

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

 

[rwong]

PERFECT!

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