thomasbeard
Board Regular
- Joined
- Oct 19, 2005
- Messages
- 139
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
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