Excel 2007 slow to copy entire sheet

[Guest]

When I right click on a sheet and select "copy" and copy the sheet to another
workbook it takes Excel nearly 30-60 seconds to complete the operation.

The sheet being copied contains only data, about 15 columns x 400 rows.

This behavior is exhibited when copying from workbooks that are in the new
and old file formats.

I have a new Intel Duo-Core 2.66Ghz and 3Gb RAM.

Any suggestions would be appreciated.

 

[Guest]

I just filled 15 columns x 400 rows with the address of each cell and copied
the sheet to the end of the workbook and it was almost instantly done. Intel
Duo-Core 2.40 GHz (E6600) w/2GB RAM on Vista Ultimate. Do you have a lot of
formulas that have to be recalculated on that sheet?

One thing to look at might be in the Options | Advanced area - scroll down
to the [Formulas] section and make sure it has allow multi-threaded
calculations ticked along with allowing it to use all available CPUs on the
system. That may help.

 

[Guest]

I did some additional testing and investigating. I have a macro that imports
the data on the problem sheet from a ftp site. Each time I have run that
macro Excel has created a new web query connection between the ftp site and
the worksheet. So now after running the macro for several years, Excel has
accumulated nearly 4000 connections. And unlike Excel 2003, Excel 2007 is
having a hard time copying all those connections.

Now I just need to figure out how to keep Excel from adding a connection
every time I run the macro.

I just filled 15 columns x 400 rows with the address of each cell and copied
the sheet to the end of the workbook and it was almost instantly done. Intel
Duo-Core 2.40 GHz (E6600) w/2GB RAM on Vista Ultimate. Do you have a lot of
formulas that have to be recalculated on that sheet?

One thing to look at might be in the Options | Advanced area - scroll down
to the [Formulas] section and make sure it has allow multi-threaded
calculations ticked along with allowing it to use all available CPUs on the
system. That may help.

When I right click on a sheet and select "copy" and copy the sheet to another
workbook it takes Excel nearly 30-60 seconds to complete the operation.

The sheet being copied contains only data, about 15 columns x 400 rows.

This behavior is exhibited when copying from workbooks that are in the new
and old file formats.

I have a new Intel Duo-Core 2.66Ghz and 3Gb RAM.

Any suggestions would be appreciated.

 

[Guest]

And to answer my own question, to delete all those connections and keeping
them from accumulating I added the following lines to my macro that retrieves
data from the ftp site:

' On the Workspace worksheet, clear all existing query tables
For Each QT In ActiveSheet.QueryTables
QT.Delete
Next QT

I did some additional testing and investigating. I have a macro that imports
the data on the problem sheet from a ftp site. Each time I have run that
macro Excel has created a new web query connection between the ftp site and
the worksheet. So now after running the macro for several years, Excel has
accumulated nearly 4000 connections. And unlike Excel 2003, Excel 2007 is
having a hard time copying all those connections.

Now I just need to figure out how to keep Excel from adding a connection
every time I run the macro.

I just filled 15 columns x 400 rows with the address of each cell and copied
the sheet to the end of the workbook and it was almost instantly done. Intel
Duo-Core 2.40 GHz (E6600) w/2GB RAM on Vista Ultimate. Do you have a lot of
formulas that have to be recalculated on that sheet?

One thing to look at might be in the Options | Advanced area - scroll down
to the [Formulas] section and make sure it has allow multi-threaded
calculations ticked along with allowing it to use all available CPUs on the
system. That may help.

When I right click on a sheet and select "copy" and copy the sheet to another
workbook it takes Excel nearly 30-60 seconds to complete the operation.

The sheet being copied contains only data, about 15 columns x 400 rows.

This behavior is exhibited when copying from workbooks that are in the new
and old file formats.

I have a new Intel Duo-Core 2.66Ghz and 3Gb RAM.

Any suggestions would be appreciated.