Extremely slow sheet copy

[Kruncher]

Using XL2003, XP Pro SP2, Duo Core laptop

I've been using this line of code for years now:

Worksheets("Report").Copy
after:=Workbooks(strDestinationBook).Worksheets(Workbooks(strDestinationBook).Worksheets.Count)

The "Report" uses less than 300 rows and until recently it took mere
seconds to copy the sheet with the quoted code above.

Both the active workbook and the destination file have been saved to a
local drive - no network files in use.

Today it's taking almost 4 minutes. No software has been installed -
that I know about, corporate laptop and all - who knows what IT does
silently behind the scenes.

On a (possibly) related note, Excel "hangs" for about 20 seconds every
time I open a file - after it recalculates - I can tell with larger
files. The "pause" is consistent regardless of file size.

Any ideas as to how to get this back to normal will be very much
appreciated, as I use this sort of operation about 30 times on the
first business day of every week. 30 occurences x 4 minutes each =
high PITA factor.

 

[Barb Reinhardt]

Do you have calculation set to automatic and screenupdating on?

Barb Reinhardt

 

[Kruncher]

At this stage of the report generation, probably. The screenupdating
is likely off, but I need to check. I'll turn both off if they're on.
Still, it's odd why it's an all-of-a-sudden type of problem.

Will revise the code as necessary and post results here.

Kruncher

 

[Jim Cone]

Try emptying the windows temp file...
Start | Run | %temp%

Also, deleting the Excel11.xlb file can speed up file opening.
It automatically rebuilds itself after deletion.
Do it after unchecking all add-ins in Tools | Add-ins and with Excel closed.
(you will lose all menu and toolbar customizations)
On my system it is located here...
C:\Documents and Settings\name\Application Data\Microsoft\Excel
--
Jim Cone
Portland, Oregon USA



"Kruncher"
wrote in message
Using XL2003, XP Pro SP2, Duo Core laptop

-snip-
Today it's taking almost 4 minutes. No software has been installed -
that I know about, corporate laptop and all - who knows what IT does
silently behind the scenes.

On a (possibly) related note, Excel "hangs" for about 20 seconds every
time I open a file - after it recalculates - I can tell with larger
files. The "pause" is consistent regardless of file size.

Any ideas as to how to get this back to normal will be very much
appreciated, as I use this sort of operation about 30 times on the
first business day of every week. 30 occurences x 4 minutes each =
high PITA factor.

 

[Kruncher]

At long last, an update to this thread and, yes, even a final
resolution!

First, thanks again for the ideas Jim. I tried them all and more,
unfortunately without success.

It got so bad that the sheet copy was taking a full 10 minutes. Again,
my processes do over 30 sheet copies.

So the solution... I work on a large network, and at least one of the
network printers in my printer list was causing the delay. I deleted
some 10 printers from my system, and the sheet copy now takes a
completely normal 6 seconds.

So there you go. In the end it had absolutely nothing to do with
Excel, and I swear that I didn't notice any other problems on my
system using any other software. One to file away for reference when
some poor soul asks the same question some day. Only list active
printers on your system!!

Kruncher