Error msg on "On Error GoTo BadFile"

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,940
Office Version
  1. 365
Platform
  1. Windows
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?
Code:
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
what if you put that code right above this line

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,329
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
'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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,100
Messages
5,768,095
Members
425,452
Latest member
htay44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top