Printing worksheets in a Workbook (Excel 97/SR2)

[steve69]

I have written a procedure that is supposed to cycle through all of the worksheets in the active workbook and print any that meet the criteria in the code. I keep getting a run time error when running this procedure. Could someone review my procedure and see if I am missing anything. I have been able to use the oSheet.PrintOut method in other procedures with success, but I can't seem to get it to work in this one.

<pre>Sub PrtWksht()
' Created by Steven M Henderson on August 21, 2001
' This procedure will cycle through the worksheets of the active
' workbook and print all of the CPWU worksheets ignoring the
' built in worksheets used to create everything else
Dim oSheet As Worksheet
For Each oSheet In Worksheets
If oSheet.Name <> "Template" _
And oSheet.Name <> "RCList" _
And oSheet.Name <> "Template(2001)" _
And oSheet.Name <> "List" _
And oSheet.Name <> "DataLibrary" _
And oSheet.Name <> "DataLib" Then
oSheet.PrintOut
Else
'fall through by design
End If
Next oSheet
End Sub
</pre>


I have also attached a screenshot of the error message.

Thanks

[pieter]

hi Steve,

you probably have a hidden sheet somewhere that matches the criteria.

either you set those to visible before you print them (oSheet.visible=true) or you don't print them at all :

if osheet.visible then osheet.printout

[steve_skelton13]

I found a really handy piece of code that will go thru your workbook and create a custom print sheet dialog box with checkboxes for each sheet so that users can pick and choose what they want to print. You will have to modify it to suit your needs but I am real happy with it:

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet

Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible And CurrentSheet.Name <> "Select a Follow Up" Then <font color=448800>' test condition to weed out sheets you don't want listed</font color=448800>
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

' Reactivate original sheet
Sheets("CRCG Meeting Information").Activate
End Sub

works real well. try it out -- you may find it is perfect for your needs.

[steve69]

Thank you very much. By adding the red code, it now works. I should have known this, as I have another procedure that loops thru all the worksheets and then prints the selected sheets a specified number of times. I cut/pasted the red code from my other procedure.

<pre>Sub PrtWksht()
' Created by Steven M Henderson on August 21, 2001
' This procedure will cycle through the worksheets of the active
' workbook and print all of the CPWU worksheets ignoring the
' built in worksheets used to create everything else
Dim oSheet As Worksheet
For Each oSheet In Worksheets
If oSheet.Name <> "Template" _
And oSheet.Name <> "RCList" _
And oSheet.Name <> "Template(2001)" _
And oSheet.Name <> "List" _
And oSheet.Name <> "DataLibrary" _
And oSheet.Name <> "DataLib" Then
<font color=red>If oSheet.Visible = xlSheetVisible Then
oSheet.PrintOut
Else
'fall through by design
End If</font color=red>
Else
'fall through by design
End If
Next oSheet
Next x
End Sub
</pre>


I appreciate your quick response.