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)
