I have a macro that copys values and deletes some sheets, then saves a copy.. I'll post the code below, but the code itself is not the problem.. it works exactly as it should.
The problem is that after it runs, excel shuts down and I get the message "excel has encountered a problem.." and something about needing to shut down and do I want to recover the file (this is in Excel 2010).
the STRANGEST part is that if I run the macro from the macro list, or from the visual basic editor... I DON'T encounter the problem..
however, if I use the command button that I set up, the macro still runs perfectly fine... but after running, excel shuts down!!
This is super frusterating, any help would be extremely appreciated!
Sub ValuedCopy()
Dim i As Integer, WkbName As String
Application.ScreenUpdating = False
For i = 2 To 5
Sheets(i).Select
Cells.Copy
Cells.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Range("F1").Select
Next i
Application.DisplayAlerts = False
Sheets(1).Delete
For i = 11 To 5 Step -1
Sheets(i).Delete
Next i
Application.DisplayAlerts = True
Sheets(1).Select
WkbName = ThisWorkbook.FullName
WkbName = Left(WkbName, Len(WkbName) - 5) & "_vc"
ThisWorkbook.SaveAs (WkbName)
Application.ScreenUpdating = True
End Sub
The problem is that after it runs, excel shuts down and I get the message "excel has encountered a problem.." and something about needing to shut down and do I want to recover the file (this is in Excel 2010).
the STRANGEST part is that if I run the macro from the macro list, or from the visual basic editor... I DON'T encounter the problem..
however, if I use the command button that I set up, the macro still runs perfectly fine... but after running, excel shuts down!!
This is super frusterating, any help would be extremely appreciated!
Sub ValuedCopy()
Dim i As Integer, WkbName As String
Application.ScreenUpdating = False
For i = 2 To 5
Sheets(i).Select
Cells.Copy
Cells.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Range("F1").Select
Next i
Application.DisplayAlerts = False
Sheets(1).Delete
For i = 11 To 5 Step -1
Sheets(i).Delete
Next i
Application.DisplayAlerts = True
Sheets(1).Select
WkbName = ThisWorkbook.FullName
WkbName = Left(WkbName, Len(WkbName) - 5) & "_vc"
ThisWorkbook.SaveAs (WkbName)
Application.ScreenUpdating = True
End Sub