Excluding worksheets from printing

[ben_hur]

Hi,

I currently embed an Excel macro in my spreadsheets which will print
the entire workbook (40+ sheets) should the user request it:

Private Sub CommandButton2_Click()
Dim Sheet As Worksheet
Dim lAnswer As Long
lAnswer = MsgBox("This report contains " & Sheets.Count & " sheets - Do
you want to print them all?", vbYesNo, "Print?")
If lAnswer = vbNo Then
Exit Sub
Else
Worksheets.Select
Application.Dialogs(xlDialogPrint).Show
Sheets("Total").Select
End If
End Sub


However, I have now incorporated several workings sheets that are
hidden which I do not want to be printed - the only problem is I don't
know how to alter the above code to exclude the sheets (called "Input"
and "Workings"). Can anybody help?

 

[Bob Phillips]

Private Sub CommandButton2_Click()
Dim Sheet As Worksheet
Dim lAnswer As Long
Dim lSheet As Long
Dim sh As Worksheet
Dim arySheets
lAnswer = MsgBox("This report contains " & Sheets.Count & _
" sheets - Do you want to print them all?", vbYesNo, "Print?")
If lAnswer = vbNo Then
Exit Sub
Else
ReDim arySheets(1 To 1)
For Each sh In ActiveWorkbook.Worksheets
If sh.Visible = xlSheetVisible Then
lSheet = lSheet + 1
ReDim Preserve arySheets(1 To lSheet)
arySheets(lSheet) = sh.Name
End If
Next sh
Worksheets(arySheets).Select
Application.Dialogs(xlDialogPrint).Show
Sheets("Total").Select
End If
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[ben_hur]

Thank you!!!