ExcelBanter - set up a command button to print multiple workbook

It is hard to give an answer without seeing the code in question, but
an error 9 is a "subscript out of range" error. This occurs when you
are attempting to access a member of a collection when that member
does not exist. For example,

Worksheets("Sheet6").PrintOut

will throw an error 9 if there is no sheet named "Sheet6" because you
are attempting to access an item in the Worksheets collection that
doesn't exist.

If the problem is indeed a non-existent worksheet, you can use the
following function to test if the sheet exists:

Function SheetExists(SheetName As String, _
Optional WB As Workbook) As Boolean
On Error Resume Next
SheetExists = CBool(Len(IIf(WB Is Nothing, ThisWorkbook, WB). _
Worksheets(SheetName).Name))
End Function

Then, in your code, do something like

If SheetExists("Sheet6") = True Then
Worksheets("Sheet6").PrintOut
Else
' sheet does not exist
End If

If you need to test whether a workBOOK rather than a workSHEET exists,
use the following function:

Function WorkbookExists(WBName As String) As Boolean
Dim WB As Workbook
On Error Resume Next
For Each WB In Application.Workbooks
If StrComp(WB.Name, WBName, vbTextCompare) = 0 Or _
StrComp(WB.FullName, WBName, vbTextCompare) = 0 Then
WorkbookExists = True
Exit Function
End If
Next WB
WorkbookExists = False
End Function

Here, WBName can be either just the name (e.g., "Book1.xls") or the
full file path of the workbook name (e.g., "C:\Folder\Test\Book1.xls")

You'd use this code like

If WorkbookExists("Book1.xls") = True Then
' workbook exists. do something
Else
' workbook does not exist. do something else.
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Thu, 27 Aug 2009 14:55:01 -0700, casper
wrote:

i put a commad button on a worksheet o i could hit it t print multiple
workbooks.
it prints only a few thengives me an error code of 9.cannot fix