Excel App written in 2003 hangs when run in 2007

Question

I have an Excel application that I wrote using Excel 2003.  The process begins by opening a file to be processed, call that File A.  Then it opens File B, selects a worksheet, copies that worksheet into File A, renames the sheet, and goes on.  When I ran the process under Excel 2007, it would hang somewhere between the 'copy the sheet' step and the 'rename the sheet' step without giving me a run-time error.

In the process of troubleshooting, I stepped through the code, and it ran all the way through without error.  I went back and ran it normally, and got the hang again.

I went back into the code and inserted Message Boxes just after the 'copy' step and just after the 'rename' step.  Again, except for pausing for operator response on the message boxes, the code ran normally.

My last experiment was to remove the message boxes and insert a

For I = 1 to 10
Next

between the 'copy' step and the 'rename' step.  This also allows the system to function normally.

The following code hangs:

    Workbooks.Open Filename:=ToolSetPath & "\TrnRecordsWH.xlsm"
    RecordsWorkbook = ActiveWorkbook.Name
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy After:=Workbooks(AnalysisWorkbook).Sheets(NumSheets)
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "EmpTrnRecordsWH"
    Workbooks(RecordsWorkbook).Close SaveChanges:=False

The following code works:

    Workbooks.Open Filename:=ToolSetPath & "\TrnRecordsWH.xlsm"
    RecordsWorkbook = ActiveWorkbook.Name
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy After:=Workbooks(AnalysisWorkbook).Sheets(NumSheets)
    For I = 1 to 10
    Next
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "EmpTrnRecordsWH"
    Workbooks(RecordsWorkbook).Close SaveChanges:=False

Answer 1

I would recommend posting your question to the VBA forum:

http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

---

Paul ~~~~ Microsoft MVP (Visual Basic)