Automation error occurs UNLESS Visual Basic Window is open

%3CLINGO-SUB%20id%3D%22lingo-sub-3353284%22%20slang%3D%22en-US%22%3EAutomation%20error%20occurs%20UNLESS%20Visual%20Basic%20Window%20is%20open%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3353284%22%20slang%3D%22en-US%22%3E%3CP%3EI%20run%20a%20short%20bit%20of%20code%20upon%20opening%20my%20workbook.%26nbsp%3B%20I%20have%20a%20button%20that%20runs%20more%20code.%26nbsp%3B%20The%20first%20bit%20of%20code%20runs%20just%20fine%2C%20copying%20three%20worksheets%20from%20another%20workbook%20to%20this%20one.%26nbsp%3B%20When%26nbsp%3B%20I%20hit%20the%20button%20to%20run%20my%20other%20Macro%2C%20I%20get%20an%20Automation%20error%20and%20it%20crashes%20out%2C%20UNLESS%26nbsp%3BI%20have%20the%20VB%20window%20open%2C%20in%20which%20case%20it%20works%20just%20fine.%3C%2FP%3E%3CP%3EI%20can%20run%20this%20all%20day%20long%2C%20but%20if%20I%20just%20open%20and%20run%20from%20the%20button%20or%20the%20Macro%20Dialog%20box%2C%20no%20dice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20is%20the%20Code%20that%20is%20run%20upon%20opening.%26nbsp%3B%20Am%20I%20doing%20something%20wrong%20here%3F%26nbsp%3B%20How%20can%20I%20fix%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanx%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPhil%3C%2FP%3E%3CP%3E%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3D%3C%2FP%3E%3CP%3ESub%20LoadATSWorksheets()%3C%2FP%3E%3CP%3EApplication.DisplayAlerts%20%3D%20False%3C%2FP%3E%3CP%3ESet%20closedBook%20%3D%20Workbooks.Open(%22s%3A%5Cit%5Cdatabases%5Cptw%5CATSReportsNEW.xlsx%22)%3CBR%20%2F%3EclosedBook.Sheets(%22ATS_Bottoms_WE_with_Images%22).Copy%20Before%3A%3DThisWorkbook.Sheets(1)%3CBR%20%2F%3EclosedBook.Close%20SaveChanges%3A%3DFalse%3C%2FP%3E%3CP%3ESet%20closedBook%20%3D%20Workbooks.Open(%22s%3A%5Cit%5Cdatabases%5Cptw%5CATSReportsNEW.xlsx%22)%3CBR%20%2F%3EclosedBook.Sheets(%22ATS_Hats_WE_with_Images%22).Copy%20Before%3A%3DThisWorkbook.Sheets(1)%3CBR%20%2F%3EclosedBook.Close%20SaveChanges%3A%3DFalse%3C%2FP%3E%3CP%3ESet%20closedBook%20%3D%20Workbooks.Open(%22s%3A%5Cit%5Cdatabases%5Cptw%5CATSReportsNEW.xlsx%22)%3CBR%20%2F%3EclosedBook.Sheets(%22ATS_Tee_Sweat_Jacket_WE_With_Im%22).Copy%20Before%3A%3DThisWorkbook.Sheets(1)%3CBR%20%2F%3EclosedBook.Close%20SaveChanges%3A%3DFalse%3CBR%20%2F%3E%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3353284%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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