VBA Script in Excel Stalls Out On It's Own After Opening Secondary Excel File
 

Hey everyone,
I'm having a bit of a problem with a bit of VBA code that I've got

going on in an Excel File. Basically, the code that I have set up is
supposed to open a specific file, highlight and copy a range of cells,
the go back to the code's host (excel) file and paste those cells in
there. The problem I'm having is that as soon as I programatically
open up that second file, the script just stops running as if it came
up against the "End sub" command. I've tries eberything that I can
think of, but nothing seems to make any difference. I've posted a bit
of the script below. If any of you can see what I'm doing wrong or
could make some other suggestion it would bve greatly appreciatted.
Thanks.


Code:
'The following code will open the newly created xls file and move all
of the needed data values first to the "Conversion"
'sheet, and then copy the converted data to the "Output" sheet of this
workbook
'Open the newly exported excel file and copy the relevant data
Workbooks.Open Filename:="H:\TeamLead\00u7735\Scripts\ScriptOutpu t\" &
intAgentName & ".xls"
Windows(intAgentName & ".xls").Activate
Sheets(intAgentName).Select
Range("B3:U3").Select
Selection.Copy
'Paste it into this file on the conversino page
Windows("ABC Scripting Automation.xls").Activate
Sheets("Conversion").Select
Range("A2:T2").Select
Selection.Paste
'Copy the converted data to the Final Output page
Range("A13:T13").Select
Selection.Copy
Sheets("Output").Select
'Paste it to a row whose number is based on the counter variable
Range("B" & intCounter + 2 & ":U" & intCounter + 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("A" & intCounter + 2).Select
ActiveCell.Value = intAgentName
'Close the exported Excel file
Windows(intAgentName & ".xls").Activate
ActiveWorkbook.Close
Windows("ABC Scripting Automation.xls").Activate

VBA Script in Excel Stalls Out On It's Own After Opening Secondary
 

Hi SilentThunder,

You might use if statement, do while and exit sub to accomplish what you are
trying to achieve. I think you can able to achieve it don't give up hope.

"SilentThunder" wrote:

Hey everyone,
I'm having a bit of a problem with a bit of VBA code that I've got

going on in an Excel File. Basically, the code that I have set up is
supposed to open a specific file, highlight and copy a range of cells,
the go back to the code's host (excel) file and paste those cells in
there. The problem I'm having is that as soon as I programatically
open up that second file, the script just stops running as if it came
up against the "End sub" command. I've tries eberything that I can
think of, but nothing seems to make any difference. I've posted a bit
of the script below. If any of you can see what I'm doing wrong or
could make some other suggestion it would bve greatly appreciatted.
Thanks.


Code:
'The following code will open the newly created xls file and move all
of the needed data values first to the "Conversion"
'sheet, and then copy the converted data to the "Output" sheet of this
workbook
'Open the newly exported excel file and copy the relevant data
Workbooks.Open Filename:="H:\TeamLead\00u7735\Scripts\ScriptOutpu t\" &
intAgentName & ".xls"
Windows(intAgentName & ".xls").Activate
Sheets(intAgentName).Select
Range("B3:U3").Select
Selection.Copy
'Paste it into this file on the conversino page
Windows("ABC Scripting Automation.xls").Activate
Sheets("Conversion").Select
Range("A2:T2").Select
Selection.Paste
'Copy the converted data to the Final Output page
Range("A13:T13").Select
Selection.Copy
Sheets("Output").Select
'Paste it to a row whose number is based on the counter variable
Range("B" & intCounter + 2 & ":U" & intCounter + 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range("A" & intCounter + 2).Select
ActiveCell.Value = intAgentName
'Close the exported Excel file
Windows(intAgentName & ".xls").Activate
ActiveWorkbook.Close
Windows("ABC Scripting Automation.xls").Activate