Faster closing of large spreadsheet

[Steve]

I use a Trend.xls to compile several other spreadsheets into. I copy and
paste some data into the master, in this case Trends.xls. When I close
"Impromptu DataDump rev3 2005 to present.xls", it takes up to 15 minutes. It
does not have any "upon close" code. Is there a better (faster) method to use
than the sample I have included below?
Thanks Steve

Workbooks.Open Filename:= _
(WhereDump & "Impromptu DataDump rev3 2005 to present.xls"):
ReadOnly = True
Worksheets("Sheet1").Range("A2:BW40000").Select
Selection.Copy
Workbooks("Trends.xls").Activate
Worksheets("Product_Code").Range("A2").Select
ActiveSheet.Paste
Application.StatusBar = "Closing Data Dump........."
Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close
SaveChanges:=False

 

[JP]

You don't need to select cells before copying them. The Copy Method
has a Destination argument where you can specify where you want the
copied values to be pasted.

Worksheets("Sheet1").Range("A2:BW40000").Copy Destination:=Workbooks
("Trends.xls").Sheets("Product_Code").Range("A2")

Also, do you have a lot of formulas in "Impromptu DataDump rev3 2005
to present.xls"? If so, they're probably recalculating when you do the
copy.

--JP

 

[Steve]

There are no formulas in the DataDump file that I am trying to close. It is
not the copy that is taking so long but the Close. Thanks for the Copy
Destination idea though.
Steve

 

[Dave Peterson]

I've never seen a workbook take that long to close -- especially if I'm closing
it without saving.

If you open it manually and close it manually, does it work ok?

Just a silly guess...

Are there any events that could be running when you close that workbook?

application.enableevents = false
Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close _
SaveChanges:=False
application.enableevents = true

And even a sillier guess...

Clean up your windows temp folder.

Try closing excel and cleaning up your windows temp folder--it might help and it
can't hurt.

windows start button|Run
%temp%
is a quick way to get there.

 

[Steve]

Hi Dave
Manually closing is seconds. THere are no events (No code at all just data).
I moved the close statement after a block of code that opens and closes a
third spreadsheet (which closes quickly) and it closes very quickly when the
Close statement is in this new location.

 

[JP]

I just noticed that your Open statement and Close statement have
different workbook names.

Workbooks.Open Filename:=(WhereDump & "Impromptu DataDump rev3 2005 to
present.xls")

Workbooks("Impromptu DataDump rev3 2005 to present.xls").Close

--JP