excel 2003 very unstable after copying a worksheet

[Brian Murphy]

I am finding that excel 2003 becomes very unstable after I manually
copy a worksheet. VBA macros that normally run fine halt before
finishing, and excel crashes soon thereafter.

Does anyone have any ideas why this would happen?

I am using XL 2003 SP2. When I try to install SP3 the updater
complains that it can't find a file that it needs on my original
XL2003 CD.

Thanks,

Brian

 

[Peter T]

Normally that shouldn't be a problem, of course. Before copy, do any/all the
following in stages as appropriate.

- Comment any code behind the worksheet and remove any ActiveX controls on
the sheet.

- Remove CF & DV

- Replace formulas with values

Regards,
Peter T

 

[Brian Murphy]

Thank you for the reply, Peter.

The problem does not appear to be specific to any worksheet.
None of the sheets in my files have any code in their code modules.
There are no ActiveX controls on the sheets.
The problem is reproduceable on another computer with xl2003SP3.
I haven't tried Xl2007 yet. I don't know what to expect there.

What do you mean by CF and DV?

I have a collection of addins containing a great many macros. After a
sheet copy, some of the macros simply stop running before they are
finished, and many generate run time errors on different kinds of
statements.

One thing that always happens, is right after any the above problems
happens, excel becomes unstable. Can't select cells on a worksheet,
stuff like that. And then closing Excel triggers an outright crash.

I distribute my addins to a great many users, and I see this as a huge
problem.

Thanks,

Brian

 

[Jim Cone]

I think Peter went to bed. CF...conditional formatting, DV...data validation.
I have not seen any reference that says XL2007 performs better at anything.

For what it's worth...
http://support.microsoft.com/kb/210684/en-us
"Copying worksheet programmatically causes run-time error 1004 in Excel"
http://support.microsoft.com/kb/215573/en-us
"You receive an error message when you add a chart to a workbook in Excel"

Moreover, does the problem stop after unchecking all add-ins in Tools | Add-ins?
If so, add them back, one at a time, until the problem occurs.
You will then have a culprit. Repeat the process until all add-ins are vetted.
--
Re: updating Office 2003.
The worked for me...
Go to start | run | type services.msc
Search for office source engine
Change its status to automatic from disabled
--
Jim Cone
Portland, Oregon USA




"Brian Murphy"
wrote in message
Thank you for the reply, Peter.
The problem does not appear to be specific to any worksheet.
None of the sheets in my files have any code in their code modules.
There are no ActiveX controls on the sheets.
The problem is reproduceable on another computer with xl2003SP3.
I haven't tried Xl2007 yet. I don't know what to expect there.

What do you mean by CF and DV?
I have a collection of addins containing a great many macros. After a
sheet copy, some of the macros simply stop running before they are
finished, and many generate run time errors on different kinds of
statements.
One thing that always happens, is right after any the above problems
happens, excel becomes unstable. Can't select cells on a worksheet,
stuff like that. And then closing Excel triggers an outright crash.
I distribute my addins to a great many users, and I see this as a huge
problem.
Thanks,
Brian

 

[Peter T]

Jim was spot on about CF & DV, but not about going to bed! As I write it's
late afternoon here.

I don't have any more ideas until you've (Brian) checked out Jim's
suggestions.

Regards,
Peter T

 

[Brian Murphy]

I unchecked all addins, and moved all addins out of XLSTART. The VBA
project explorer then showed a clean slate - only Book1.xls was in the
list.

Back in Excel there is still a menu item for Rob Bovey's Code Cleaner
on the Tools menu, even though it does not appear anywhere in the
excel environment that I can find. But this one ought to be safe.

The errors still start happening right after copying a worksheet
manually (using the mouse).

I have an old macro that I use to make copies of worksheets that
doesn't use COPY but MOVES the sheet to a temporary file, and then
MOVES it back twice, renaming sheets accordingly. Using this macro
does not trigger the Excel errors and crashes.

I've spent loads of time trying to figure this one out. I don't seem
to be getting anywhere. It would be good to know if this all the
fault of bugs in Excel's sheet copying methods. I don't recall this
happening with xl2000 or xl2002. I no longer have them installed
anywhere to test them.

Brian

 

[Brian Murphy]

I did my best to try what you suggested to get the SP3 to install, but
it didn't make any difference. Actually, in services there was no
option to set it to automatic. It was off, so I started it, and tried
to apply SP3, and ran into the same problem about needing a file from
the CD that's not on the CD.

Darn. Applying a SP shouldn't be so difficult.

Brian