Interrupted sheet copying causes phantom Book2

[Dan Williams]

Run this with a sheet1 that has enough calculations to make the copying
non-instantaneous.

Sheets("sheet1").Copy

If you press ESC quickly enough during the copying, you get a
not-surprising error: "Copy method of Worksheet class failed." That's
fine.

However, if sheet1 has a pie chart in it, then the next time you exit
from Excel, you get:

"Do you want to save the changes you made to 'Book2'?"

and if you naively say "Yes," Excel crashes. (And if you've tested it
several times before exiting, it also asks you about Book3, Book4,
etc.)

When I trap the ESC key, the trapping works fine, but the same error
happens when I exit Excel anyway.

I was building a mechanism to allow users to interrupt a tiresome loop,
but this makes me afraid to suggest using the ESC key at all. They
could lose stuff in other open workbooks.

I'm guessing I'm just going to have to live with this. Perhaps I'll
have to re-assemble the pie chart after each copy. But I thought I'd
at least document it.

Dan Williams
<danwPlanet>

 

[Dave Peterson]

I didn't try it, but do you have trouble if you change calculation to manual,
then copy the sheet, then change it to automatic?

 

[Dan Williams]

I just tried it with manual calculation permanently on, and it still
happens.

After posting this, I discovered that if you DON'T interrupt it, the
over-simplified statement "Sheets("sheet1").Copy" actually copies the
sheet into a newly-created Book2. This hints at where the phantom
Book2 may come from.

But the same thing does happen when you interrupt copying within the
workbook, e.g.,

Sheets("sheet1").Copy before:=Worksheets(1)

It's almost as if interrupting this statement makes processing stop
after the "Copy" and before the "before:=" and it aborts in such a way
as to sort of half-create Book2.

 

[Dan Williams]

BTW:
Excel 2000
Windows 2000

 

[Dave Peterson]

This line:
Sheets("sheet1").Copy
should create a new workbook, so it's really not much of a phantom.

But I don't have a guess why you're getting crashes.

===
Just some things to test...

Clean up the windows temp folder (can't hurt and sometimes helps)

Start excel in safe mode:
close excel
windows start button|Run
excel /safe
File|Open your workbook (macros will be disabled, though)
hit alt-f11 (to get to the vbe)
hit ctrl-g (to see the immediate window)
type this and hit enter:
Sheets("sheet1").Copy

Does it crash excel?

===
One more question: Does this happen on all pc's (if you can test)?

I don't have any guesses (no matter what your responses), but maybe someone else
will.