Working program now doesn't, can't activate workbook's worksheet to paste data

C

Cloudfall

I have written a program which has automated some boring/ repetitive/
time consuming business processes.This program worked. I am now
preparing documentation for this program with a co-worker. Today the
program didn't work properly. After stepping through the code, I found
that a column of numbers I was copying from Sheet "Suppliers with
ABN_ACN_BN" in Book "Automation V005.xls" to Sheet "ABNLookup" in Book
"ABNLookUp.xls" wasn't copying (even though it used to). Investigating
this further (with another co-worker who knows VBA), we found (using
the Watch and Immediate Windows) that the workbook/ worksheet to which
I was trying to copy the data wasn't activating. After trying
everything for two hours we finally gave up and so here I am. Here is
the actual code:

(Note: the variable "strActiveSpreadsheet" contains the spreadsheet
name "Automation V005.xls")

Workbooks(strActiveSpreadsheet).Activate
Worksheets("Suppliers with ABN_ACN_BN").Activate
Range("E2:E65530").Select
Workbooks("Automation V005.xls").Worksheets("Suppliers with
ABN_ACN_BN").Range("E2:E65530").Copy
Workbooks("ABNLookUp.xls").Activate 'THIS STEP DOESN'T WORK
Worksheets("ABNLookup").Activate

"ABNLookup" in "Automation V005.xls" is activated, not "ABNLookup" in
"ABNLookUp.xls". You know this because its tab is highlighted.

Why doesn't the step Workbooks("ABNLookUp.xls").Activate work? (The
code above is now a mixture of the code I started with this morning as
well as code from attempts to get the program to work.)
 
Top