Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extremely slow sheet copy
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(Wo rkbooks(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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extremely slow sheet copy
Do you have calculation set to automatic and screenupdating on?
Barb Reinhardt "Kruncher" wrote: 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(Wo rkbooks(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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extremely slow sheet copy
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 On Oct 14, 4:04*pm, Barb Reinhardt wrote: Do you have calculation set to automatic and screenupdating on? Barb Reinhardt "Kruncher" wrote: 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(Wo rkbooks(strDestinationBoo*k).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.- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extremely slow sheet copy
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extremely slow sheet copy
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 On Oct 14, 3:30*pm, "Jim Cone" wrote: 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 withExcelclosed.. (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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cut, copy & paste functions in Excel 2007 are extremely slow. | Excel Worksheet Functions | |||
Extremely slow copy/cut in Excel 2007 | Excel Discussion (Misc queries) | |||
Copy / Paste extremely slow | Excel Worksheet Functions | |||
Extremely slow macro | Excel Programming | |||
Copy and paste link in worksheet is extremely slow. What to doÉ | Excel Discussion (Misc queries) |