VBA "On Error" problem

[thomasbeard]

I have some code that opens files sequentially, searches for a worksheet name, if it's there it copies that sheet to a consolidated file and then close the file, if not then it should close the file without doing anything else.
I have set it up to use the code:

On Error GoTo CloseFile

to manage cases where the sheet does not exist. The "CloseFile" code closes the file. The problem I am having is strange, which is it works fine for the first error instance (i.e. when the specified sheet does not exist) but on the next error, it come up with the error code "Subscript out of range" (as if the On Error GoTo code has stopped working).
Can someone please help me understand what is going on and how it can be fixed?

Here is a copy of the relevant piece of code:

For iRow = 1 To rInitiativeList.Rows.Count
sInitiativeList = rInitiativeList.Cells(iRow, 1)
If sInitiativeList <> "" Then
sSourceFile = rInitiativeList.Cells(iRow, 1)
Workbooks.Open Filename:=myVariable & "\" & sSourceFile
Set wbSource = ActiveWorkbook

On Error GoTo CloseFile
Set wsSource = wbSource.Worksheets(sConsolidation)
wsSource.Unprotect
If Not wsSource Is Nothing Then

Set wsDest = wbDest.Worksheets(wbDest.Worksheets.Count - 1)
wsSource.UsedRange.Value = wsSource.UsedRange.Value

wsSource.Copy after:=wsDest
Set wsDest = wbDest.Worksheets(wbDest.Worksheets.Count - 1)
wsDest.Name = sInitiativeList
wsDest.Names.Add "ExpenseTemplateDeleteMe", "=""A"""
End If

CloseFile:
wbSource.Close False
End If

Next iRow
On Error GoTo 0

 

[Andrew Poulsom]

From VBA Help:

An "enabled" error handler is one that is turned on by an On Error statement; an "active" error handler is an enabled handler that is in the process of handling an error. If an error occurs while an error handler is active (between the occurrence of the error and a Resume, Exit Sub, Exit Function, or Exit Property statement), the current procedure's error handler can't handle the error. Control returns to the calling procedure. If the calling procedure has an enabled error handler, it is activated to handle the error. If the calling procedure's error handler is also active, control passes back through previous calling procedures until an enabled, but inactive, error handler is found. If no inactive, enabled error handler is found, the error is fatal at the point at which it actually occurred. Each time the error handler passes control back to a calling procedure, that procedure becomes the current procedure. Once an error is handled by an error handler in any procedure, execution resumes in the current procedure at the point designated by the Resume statement.

So you need a Resume statement to reactivate you error handler.

For more details:

http://www.cpearson.com/excel/ErrorHandling.htm

 

[thomasbeard]

That's fixed it, thanks