Select only visible sheets from an array for printing

[ashline02]

Long time lurker, first time poster

I need help figuring out how to select the visible sheets from an array so that they may be printed while excluding all other sheets, visible or not.

At all times I have at least 1 ws out of an array of 11 visible. I have 2 other ws that are always visible, but will never be printed, for a total of 13 possible ws that can be visible at any time.

Here is what I have so far:
________________________
Sub printselect()

Dim tf As Boolean
tf = True
For Each SH In Worksheets(Array(1, 3, 7, 8, 9, 10, 11, 12, 13, 14, 15))
If SH.Visible Then SH.Select tf << it hangs up here
tf = False
Next

Const xlDialogPrinterSetup = 9
Dim strOld As String
Dim strNew As String

strOld = Application.ActivePrinter
Application.Dialogs(xlDialogPrinterSetup).Show

strNew = Application.ActivePrinter

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

Application.ActivePrinter = strOld
End Sub
____________________

It may hang up other places as well, but I haven't been able to get by this as of yet. Any help would be much appreciated. Let me know if you need more info on any part. Thanks!

The Noob

 

[Tom Urtis]

Without getting into the array side of the issue, generally addressing your question (if I read it correctly), you can loop through selected sheets like this:


Dim sh as Object
for each sh in ActiveWindow.SelectedSheets

'print or edit yada yada

Next sh

 

[mikerickson]

The problem is that the first time through the loop, tf is set to false and it remains that way for all the other loops.

Also, xlDialogPrinterSetup is a system constant and can't be reset, so that line should be removed.

Dim SH as Worksheet
Dim strOld as String, strNew as String

strOld = Application.ActivePrinter
Application.Dialogs(xlDialogPrinterSetup).Show

strNew = Application.ActivePrinter

For Each SH In Worksheets(Array(1, 3, 7, 8, 9, 10, 11, 12, 13, 14, 15))
    If SH.Visible = xlSheetVisible Then
        SH.PrintOut  Copies:=1, Collate:=True 
        Exit For
    End If
Next SH

Application.ActivePrinter = strOld

 

[p45cal]

It seems to work OK here, the only thing that I'd consider changing is:

If sh.Visible Then sh.Select tf '<< it hangs up here
tf = False

to:

If sh.Visible Then 
   sh.Select tf '<< it hangs up here
   tf = False
end if

as if the first sheet it come across IS hidden, tf will still be set to false, rather than only after the first visible sheet in the array.

If any of your tested sheets are xlVeryHidden, it may fail because Sheet.Visible may not return what you expect, insted use:

If sh.Visible = -1 Then

 

[p45cal]

Somewhere in the recesses of my brain I think that printing out one sheet at a time is not quite the same as printing out selected sheets - something to do with the number of print jobs and page numbering - I think with the Selectedsheets printout you get continuous page numbering, and I also think there's another difference, but can't dig it out - perhaps to do with print preview?

 

[ashline02]

@ p45cal

The "-1" did the trick. I learned something new Thank you so much!! And thank you to the other responders as well, I really appreciate the help!

A little less noob