Error handler not working

[Russj62]

I have code that among other things, copies an excel file from our server, renames it and places it into another file. Occasionally remote users get errors at this "FileCopy Source" line. My assumption is that there was a data transfer issue (or other issue) that caused the file to not be copied. So, I have been attempting to write error handling to 1. attempt the "FileCopy Source" one more time, 2. if that causes another error, notify the user of the error and to remove some information that was entered into a worksheet via previous code. On the "FileCopy Sourse" line, I have purposefully changed the file name to one that does not exist to force an error. I have been messing with the error handling all day and can not get it to work. I simply get the File Not Found error. In my latest attempt to simplify, I have removed one error handler and just hoping to get "Punt" to work for starters.

Any help would be greatly appreciated.

     'Add SCCO Workbook
        NewSCCO = SCLogPath & "\" & FixSubcontractor & Number & ".xls"
        'ATTEMPT TO CORRECT ERRORS THOUGHT TO BE CAUSED BY DATA CONNECTION
        On Error GoTo Punt
        FileCopy Source:="H:\WSBCC Forms\SCCO Template\SCCOx.xls", Destination:=NewSCCO
        On Error GoTo 0
'
'
'
'many lines of working code
'
'
'
Exit Sub
'SCcoError:
'        'On Error GoTo Punt
'        FileCopy Source:="H:\WSBCC Forms\SCCO Template\SCCO.xls", Destination:=NewSCCO
'        On Error GoTo 0
'    Resume Next
Punt:
    Worksheets("SC Log").Unprotect "2132"
    Range(ItemRow).Clear
    Worksheets("SC Log").Protect "2132"
    ActiveWorkbook.Save
    MsgBox "There was an error that prevented the creation of the Subcontract CO file.  Please try again.  If the problem persists, contact Admin"
    Resume Next
End Sub

 

[Russj62]

So in another sub errorhandling works fine, in this sub it doesn't...

Then it has to be something in the code before... the part not shown... can you show more code? The whole sub?

Here is the whole code. I do have one purpose built error handler that is in the previous code. Maybe something with that error handler is causing the problem. Do I need a "Resume" somewhere?:

Sub AddNewSC()
SCLogPath = ActiveWorkbook.Path
SCLogFullName = ActiveWorkbook.FullName
Dim Rng As Range
Dim Number, ItemRow As Long
TryNewNumber:
ufAddSC.Show
Select Case ufAddSC.Tag
    Case 1
    'Paste to log
        Worksheets("SC Log").Activate
        Worksheets("SC Log").Unprotect "2132"
'        If Range("b7").Formula = "" Then
'            ItemRow = 7
'            GoTo 10
'       End If
'        ItemRow = GetLastRow("B")
'10:
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
ItemRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Number = ufAddSC.tbNumber.Value
With Sheets("SC Log").Range("A7" & ":" & "A" & LastRow)

Set Rng = .Columns(1).Find(What:=Number, After:=Range("A7"), LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)
'If Number is not existing then we GoTo adding Subcontract
On Error GoTo AddSC
SCRow = Rng.Row
On Error GoTo 0
If Not Range("B" & SCRow) = vbNullString Then
    MsgBox "That SC Number is not available.  Please select an unused SC Number."
    ufAddSC.tbNumber = ""
    GoTo TryNewNumber
AddSC:

        TrueFalse = ufAddSC.lbType.Value
        If TrueFalse = True Then
            UnitPrice = "Unit Price"
        End If
        Range("A" & ItemRow).Value = Number
        Range("B" & ItemRow).Value = ufAddSC.tbSubcontractor.Value
        Range("C" & ItemRow).Value = Left(ufAddSC.cbCode.Value, 7)
        Range("D" & ItemRow).Value = ufAddSC.tbItem.Value
        Range("E" & ItemRow).Value = ufAddSC.tbAmount.Value
        Range("F" & ItemRow).Value = ufAddSC.tbDate.Value
        Range("G" & ItemRow).Value = ufAddSC.tbCreator.Value
        Worksheets("SC Log").Protect "2132"
        ActiveWorkbook.Save
    'Fix Subcontractor name for file name
        FixSubcontractor = ufAddSC.tbSubcontractor.Value
        MyArray = Array("<", ">", "|", "*", "/", "\", "?", ":", "[", "]")
        For X = LBound(MyArray) To UBound(MyArray)
            FixSubcontractor = Replace(FixSubcontractor, MyArray(X), "_", 1)
        Next X
     'Add SCCO Workbook
        NewSCCO = SCLogPath & "\" & FixSubcontractor & Number & ".xls"
        'ATTEMPT TO CORRECT ERRORS THOUGHT TO BE CAUSED BY DATA CONNECTION
        On Error Resume Next
        FileCopy Source:="H:\WSBCC Forms\SCCO Template\SCCOx.xls", Destination:=NewSCCO
        Do While Err.Number
            Err.Clear
            iTry = iTry + 1
            If iTry > 2 Then GoTo Punt
            FileCopy Source:="H:\WSBCC Forms\SCCO Template\SCCOx.xls", Destination:=NewSCCO
        Loop
        On Error GoTo 0
Worked:
        
            JobNo = Worksheets("SC Log").Range("B2").Value
            Project = Worksheets("SC Log").Range("B3").Value
            Subcontractor = Worksheets("SC Log").Range("B" & ItemRow).Value
            Code = Worksheets("SC Log").Range("C" & ItemRow).Value
            Item = Worksheets("SC Log").Range("D" & ItemRow).Value
            SCNo = Worksheets("SC Log").Range("A" & ItemRow).Value
            Amount = Worksheets("SC Log").Range("E" & ItemRow).Value
            SCDate = Worksheets("SC Log").Range("F" & ItemRow).Value
Worksheets("SC Log").Activate
Worksheets("SC Log").Unprotect "2132"
Range("A7" & ":" & "G" & ItemRow).Sort Key1:=Range("A7"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Worksheets("SC Log").Protect "2132"
End If
End With
        Workbooks.Open Filename:=NewSCCO
        Workbooks(FixSubcontractor & SCNo & ".xls").Activate
        Workbooks(FixSubcontractor & SCNo & ".xls").Unprotect "2132"
        Workbooks(FixSubcontractor & SCNo & ".xls").Worksheets("CO Log").Unprotect "2132"
        Workbooks(FixSubcontractor & SCNo & ".xls").Worksheets("CO Log").Range("C2").Value = JobNo
        Workbooks(FixSubcontractor & SCNo & ".xls").Worksheets("CO Log").Range("C3").Value = Project
        Workbooks(FixSubcontractor & SCNo & ".xls").Worksheets("CO Log").Range("D5").Value = Subcontractor
        Workbooks(FixSubcontractor & SCNo & ".xls").Worksheets("CO Log").Range("P2").Value = Code
        Workbooks(FixSubcontractor & SCNo & ".xls").Worksheets("CO Log").Range("F8").Value = Item
        Workbooks(FixSubcontractor & SCNo & ".xls").Worksheets("CO Log").Range("E4").Value = JobNo & "-S-" & SCNo
        Workbooks(FixSubcontractor & SCNo & ".xls").Worksheets("CO Log").Range("J4").Value = Amount
        Workbooks(FixSubcontractor & SCNo & ".xls").Worksheets("CO Log").Range("P4").Value = SCDate
        Workbooks(FixSubcontractor & SCNo & ".xls").Worksheets("CO Log").Range("P5").Value = ufAddSC.tbVendorNo
        Workbooks(Subcontractor & SCNo & ".xls").Worksheets("CO Log").Range("A9").Value = ufAddSC.lbType
        Sheets("CO Log").Protect "2132"
        Workbooks(FixSubcontractor & SCNo & ".xls").Protect "2132"
        ActiveWorkbook.Save
    Case 2
        'Just continue to end
End Select
Unload ufAddSC
Exit Sub
'SCcoError:
'        'On Error GoTo Punt
'        FileCopy Source:="H:\WSBCC Forms\SCCO Template\SCCO.xls", Destination:=NewSCCO
'        On Error GoTo 0
'    Resume Next
Punt:
    Worksheets("SC Log").Unprotect "2132"
    Range(ItemRow).Clear
    Worksheets("SC Log").Protect "2132"
    ActiveWorkbook.Save
    MsgBox "There was an error that prevented the creation of the Subcontract CO file.  Please try again.  If the problem persists, contact Admin"
    Resume Next
End Sub

 

[shg]

This works fine for me. None of the files exist.

Sub x()
    Dim vsFile As Variant
    
    On Error Resume Next
    For Each vsFile In Array("C:\Bob.xls", "C:\Don.xls", "C:\Joe.xls")
        Err.Clear
        Workbooks.Open vsFile
        If Err.Number = 0 Then Exit For
    Next vsFile
 
    If Err.Number Then GoTo Punt
    On Error GoTo 0
 
    ' do stuff
    Exit Sub
Punt:
    MsgBox "Punting"
End Sub

 

[Hermanito]

From your whole code, it shows you are already in the code of an errorhandler when you try to catch another error. Like shg said earlier, this doesn't work, and that is the reason of the problem.

You should try to split your subroutine into a few separate subs. Right now you are trying to do to much at once in one sub, and you are mixing errorhandling into regular code...

That first errorhandler
On Error GoTo AddSC
SCRow = Rng.Row
On Error GoTo 0
seems unnecessary. You can avoid that with an If Then structure. Keep the errorhandlers for the 'external' stuff, like file IO operations such as FileCopy, not for simple assignments like above