Excluding worksheets from printing

[[email protected]]

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)


<[email protected]> wrote in message
news:[email protected]...
> 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?
>

[[email protected]]

Thank you!!!