Excel 2007 SP2 crashes on saving after worksheet related VBA

[David Chilvers]

I have a workbook, say A, with a complex VBA application that opens a second
workbook B , deletes a worksheet in A, copies a master worksheet in A to
create a new worksheet in A and then copies the contents of a work sheet
from B into thenew worksheet in A.

This appears to work OK, but Excel crashes when trying to save it away.

I have looked at all the hotfixes and applied one that was a farily close
description but it still fails.

Any ideas?

 

[Shane Devenshire]

Hi,

Here is an idea, after you copy the template sheet into the file save and
close the file and then Excel. Reopen Excel and the file and continue. See
if this handles the problem.

I realize this may not meet your needs at present but we want to see if this
would fix the problem.

There is one sure way around this problem, code the steps to make the
template sheet and then get rid of the template sheet and run the code when
you insert a new sheet.

 

[David Chilvers]

Hi Shane

Implementing what you suggested was not easy for me given the context of the
VBA. But what your suggestion did do is make me think about how to
potentially breakdown the problemand tried and provide more information. In
doing so I narrowed down the problem and found a workaround. Below the pseudo
code presents the original programin a very simplified form:

With the workbook "A" open, I have the following VB code

Sub UpgradeWorkbook
Set wbA = ThisWorkbook
Set wbB = WorkBooks.Open("Workbook B name")
'Read information from wbA & wbB deciding which sheet to update'
set wsA_to_delete = wbA.workssheets("WorksheetToUpdate")
wsA_to_delete.delete
call wbA.worksheets("master").copy
set wsA_new=wbA.Worksheets("Master (2)")
wsA_new.name="WorksheetToUpdate"
set wsB_old=wbB.Worksheets("WorksheetToUpdate").
for ... to ... wsA_new.cels(...,...) = wbB_old.cells(...,..)
wbB.close
End Sub


Try to save wbA in Excel user interface and Excel crashed every time

What I did is pepper the major points in the program with writing a
different value to a cell and then do a wbA.Save. From this I found that
Excel crashed on the save after the 'wsA_to_delete.delete' line. On a hunch I
then put a

'wbB.close' before the delete
and a
'Set wbB = WorkBooks.Open("Workbook B name")' before the 'set
wsB_old=wbB.Worksheets("Someworksheet")'

and hey presto it worked.

I hope that this makes sense. If you need any more information/clarification
let me know.

Thanks for provoking my thought processes!