Excel Runtime error hangs application

[jazminecat23]

Hi again -

well just when I thought it was safe to deploy...

I made another copy of my excel project, without changing the code, because i need to use another array on my first sheet, and now I get a runtime error, "copy of sheets failed". It's in red where this happens below. To make it even more fun, it creates 2 "invisible" new excel workbooks that I can't close without using task manager.

Private Sub CmdRunSpreadsheet_Click()
'
'
' To create monthly budget distribution automatically

'Check for login to spreadsheet server
    If MsgBox("Are you logged in to Spreadsheet Server?", vbYesNo, "Login") = vbNo Then
    Exit Sub
    Else

 
    
'1. Get values from DistList sheet to creat individual Monthly Budget Reports
    Dim r As Range
    For Each r In Sheets("DistList").Range(Sheets("DistList").[A1], Sheets("DistList").[A1].End(xlDown))
      sDept = r.Value
      nVal1 = r.Offset(0, 1).Value
      nVal2 = r.Offset(0, 2).Value
      'nVal3 = r.Offset(0, 3).Value
    'BudgetStatus is where the processing occurs
      With Sheets("BudgetStatus")
          sName = UCase(Format(DateSerial(.[G2], .[G4], 1), "mmmyy") & Left(sDept, 3)) & nVal2
         .Cells(6, "G").Value = nVal1
         .Cells(7, "G").Value = nVal2
         '.Cells(11, "G").Value = nVal3
      End With

 '2. Unhide GXE sheet - must be unhidden for detail reports to expand correctly

     Sheets("GXE Source").Visible = True
       
  
 '3. calculate
    Application.Calculate
    
 
 '4. Generate Detail Reports
    Application.Run ("ExpandDetailReports")
    

 
'5. Hide button, worksheets, and all extraneous rows on main worksheet
    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = True
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = True
  
    cmdRunSpreadsheet.Visible = False
    'don't need to hide these, they're not copied over anyway.
    'Sheets("GXE Source").Visible = False
    'Sheets("DistList").Visible = False
    
[red]
'6. Copy sheets to new workbook
   
    Sheets(Array("BudgetStatus", "BudgetStatus with Commitments", "Trend", _
        "Detailed Report", "JE Detail")).Copy
[/red]

    Set wbNew = ActiveWorkbook
    With wbNew
        'below line works
        .Sheets("JE Detail").Columns("D:F").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("H:L").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("N:Q").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("V:Z").EntireColumn.Hidden = True
        .Sheets("JE Detail").Columns("AA:AF").EntireColumn.Hidden = True
    End With
    Sheets("BudgetStatus").Select
    
'7. Replace formulas with values in new workbook

    For Each ws In wbNew.Worksheets
      ws.Cells.Copy
      ws.Cells.PasteSpecial xlPasteValues
    Next
    
'8. save and close new workbook
        
    With wbNew
     .SaveAs "I:\SpreadsheetServer\Month End Reports" & "\" & sName & ".xls"
    .Close
    End With
 
Next

End If

'Lastly, unhide everything on the template so it's ready to use next time.

    cmdRunSpreadsheet.Visible = True
    Sheets("GXE Source").Visible = True
    Sheets("DistList").Visible = True
    
    Rows("1:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = False
    Columns("A:E").Select
    Range("A9").Activate
    Selection.EntireColumn.Hidden = False


End Sub

When I get the error and select debug, that's where the trouble begins. The above red item is highlighted - even though this works perfectly in the original spreadsheet. And it works fine for the first few items in the array.

Once this error has happened, I go back to the main excel window, and if I go to the Window menu item, I will see the sheet I'm working on,(Rdist1.xlt) and "Book 1".

If I select "Book 1" from the Window menu, it simply tiles Rdist1.xlt with empty space. Clicking on either Rdist1.xlt or on the empty space only maximizes Rdist1.xlt. I cannot get rid of the ghost of Book1 unless I end task.

Then, when I end task, I find that there is not just one ghost, but two. The one in the window menu, and another that only shows up when ending task asks if I want to save changes to Book2.

You can imagine what fun troubleshooting this has become...

Anyone have any insight for me? Thanks very much, and have a good weekend.

 

[jazminecat23]

Hi guys -

My errors still persist, and I'm wondering now if the solution is some sort of error trapping to get it out of the loop? I need to stop it from making these ghost workbooks - would something like On Error Resume Next make it reiterate through the loop? Would that then mean that items in the data list get skipped? I'd like to avoid that. I'm stuck. Skip, I know you said I need to make sure to reference every item explicitly, but all of the methods I've tried error out. Do either of you have any other ideas I can try? Is it at all possible that this problem won't happen in Excel 2003? Or am I just wishful thinking again? ;-) Thanks y'all!