Automation error occurs UNLESS Visual Basic Window is open

Frequent Visitor

I run a short bit of code upon opening my workbook.  I have a button that runs more code.  The first bit of code runs just fine, copying three worksheets from another workbook to this one.  When  I hit the button to run my other Macro, I get an Automation error and it crashes out, UNLESS I have the VB window open, in which case it works just fine.

I can run this all day long, but if I just open and run from the button or the Macro Dialog box, no dice.

 

Below is the Code that is run upon opening.  Am I doing something wrong here?  How can I fix this?

 

Thanx

 

Phil

================================================================

Sub LoadATSWorksheets()

Application.DisplayAlerts = False

Set closedBook = Workbooks.Open("s:\it\databases\ptw\ATSReportsNEW.xlsx")
closedBook.Sheets("ATS_Bottoms_WE_with_Images").Copy Before:=ThisWorkbook.Sheets(1)
closedBook.Close SaveChanges:=False

Set closedBook = Workbooks.Open("s:\it\databases\ptw\ATSReportsNEW.xlsx")
closedBook.Sheets("ATS_Hats_WE_with_Images").Copy Before:=ThisWorkbook.Sheets(1)
closedBook.Close SaveChanges:=False

Set closedBook = Workbooks.Open("s:\it\databases\ptw\ATSReportsNEW.xlsx")
closedBook.Sheets("ATS_Tee_Sweat_Jacket_WE_With_Im").Copy Before:=ThisWorkbook.Sheets(1)
closedBook.Close SaveChanges:=False

Application.DisplayAlerts = True

End Sub

 

 

0 Replies