VBA Saving worksheet (Help)

[dave8]

Please take a look at this code. I have a save button on my form that triggers this code to save a copy of the worksheet. I think the problem I'm having is that it creates another instance of Excel. That is, when I look at Windows Task Manager, I see two Excel applications running (and sometimes more than one). And, at some point, Excel hangs and I have to terminate the process. Is there something else I need to do here?

Private Sub CommandButton2_Click()
   Dim wsName As String
   Dim Wksname$
   Dim str, strLast, strFirst As String
   Dim r As Range
   Dim savewks$
   
    
   If TextBox2.Value <> "" Then
     str = Split(TextBox2.Value, ",")
     strLast = str(0)
     strFirst = str(1)
   Else
     MsgBox "There is no data to be saved.",
     Exit Sub
   End If
   
   
   
   Wksname = ActiveSheet.Name
   
   On Error Resume Next
   UnProtectSheet (Wksname$)
   Sheets("sheet1").Copy before:=Sheets(9)
   ActiveSheet.Shapes("CommandButton2").Select
   Selection.Delete
   savewks$ = ActiveSheet.Name
     
   
   
   Call clearFormfields                         
   Sheets(savewks$).Activate
   ProtectSheet(Wksname$)
End Sub

 

[Michael M]

Mate, you've got me !!
Have you tried it on another PC ?...does it crash then ?
How big is sheet1...do a CTRL + End and see where the last cell is !!
If it's huge it will cause problems, same with file size...but that should give "out of memory" error
The only other thing I can think of is there are too many apps open and it's just enough to tip it over.. .

 

[dave8]

Yes, it hangs on another similar PC as well. I didn't have any other apps running in the background. I tried that too. But, it does run without crashing on 2007 (on another PC). Let me inform you that the routine I just mentioned is not the entire Excel application. I was just focusing on THAT particular routine that copies the worksheet. I took the code and related components on another workbook, ran it and worked fine. Although I noticed it still created another Excel instance from the task manager, it didn't matter. I was able to continue. This is very puzzling to me.