Error msg on "On Error GoTo BadFile"

[gheyman]

I put an "On Error GoTo BadFile into my code. I wanted the On Error message to appear if it cant find the WorkBook or the sheet the code triggers to open.

My code runs fine and does what its suppose to - other than having the Error message pop-up every time. If I take the On Error message out of my code it runs fine, so I dont believe there is a problem with the code that would trigger the Error message.

Can anyone figure out why I am getting my Error message?

Sub Get_ENG_Hrs()
Application.DisplayAlerts = False
' Get_ENG_Hrs Macro
'
On Error GoTo BadFile
'
    Dim ws As Worksheet
    Sheets("ENG Labor Summary").Activate
    Set ws = ActiveSheet
'
    Dim path As String
    path = ThisWorkbook.path
    Workbooks.Open (path & "\ENG 1041.xls")
'
'Eng Labor
'   1041's
    Windows("ENG 1041.xls").Activate
    Sheets("ENG Labor Summary").Activate
    Cells.Select
    Selection.Copy
'   P3
    ws.Activate
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
        Cells.Select
    With Selection.Validation.Delete
    End With
    Range("A1").Select
    Range("C1").Select
    Range("C1").Value = Now
    Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
    Application.CutCopyMode = False
'ENG ODD
'   1041's
    Windows("ENG 1041.xls").Activate
    Sheets("ENG ODD Summary").Activate
    Cells.Select
    Selection.Copy
'   P3
    ws.Activate
    Sheets("ENG ODD Summary").Activate
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
        Cells.Select
    With Selection.Validation.Delete
    End With
    Range("A1").Select
    Range("C1").Select
    Range("C1").Value = Now
    Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
    Application.CutCopyMode = False
'Close ENG 1041.xls
    Windows("ENG 1041.xls").Close
'
BadFile:
MsgBox "Can not find ENG 1041.xls and/or Sheet named ENG Labor Summary"
End Sub

 

[QuietRiot]

what if you put that code right above this line

Workbooks.Open (path & "\ENG 1041.xls")

 

[QuietRiot]

8h

 

[Norie]

I'm not quite sure why you get the error, perhaps it's because of how you have error checking set in Tools>Options...

I can however tell you one thing for certain - there's no need for all that activating/selecting.

And if this is the code mentioned in your other thread that could be the reason the code is slow.

Also I would recommend not using Cells as that's including all 16777216 cells.

'Eng Labor
'   1041's
    With Workbooks("ENG 1041.xls").Sheets("ENG Labor Summary")
        With .Cells
            .Copy
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
            .Validation.Delete
        End With
        With .Range("C1")
            .Value = Now
            .NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
        End With
    End With
    Application.CutCopyMode = False