Error Handling

[Steph]

Hello. Thanks to the help of many of you, I have the procedure below. Can
someone help with error handling? The code prints out sheets within a
file - both of which are determined from cell values within a spreadsheet.
I would like to capture the error if excel opens a book and tries to print a
sheet that does not exist. Thanks in advance!

datapath = ThisWorkbook.ActiveSheet.Range("A3").Value
Set pr = ThisWorkbook.ActiveSheet.Cells(ActiveCell.Row, 3)
If Trim(pr.Value) <> "" Then
WbOpen = datapath & pr.Value
Set bk = Workbooks.Open(WbOpen)
Set rng1 = pr.Offset(0, 1).Resize(1, 50).SpecialCells(xlConstants)
For Each cell1 In rng1
If LCase(cell1.Value) = "all" Then
bk.PrintOut
Cells(pr.Row, 3).Interior.ColorIndex = 35
Exit For
Else
If Trim(cell1.Value) <> "" Then
bk.Worksheets(cell1.Value).PrintOut
End If
End If
Next
Cells(pr.Row, 3).Interior.ColorIndex = 35
End If
WbOpen = ""
bk.Close SaveChanges:=False

 

[FSt1]

hi,
selecting a sheet that does not exits is error 9:subscript out of range

at the top of your code add this...

On Error GoTo ErrorHandler

at the end of your code add something like this....

mysub_exit:
exit sub
ErrorHandler:
' you can Display error information.
MsgBox "Error number " & Err.Number & ": " & Err.Description
' or Resume with statement following occurrence of error.
If Err.Number = 9 Then
MsgBox ("Sorry, that sheet doesn't exist. Try Again")
Resume Mysub_Exit
End If
end sub

There are other way to handle errors but this will get you started.
in xl help type error handler for more info.

regards
FSt1