Faster closing of large spreadsheet
 

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

Faster closing of large spreadsheet
 

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

On Nov 26, 10:56 am, Steve wrote:
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

Faster closing of large spreadsheet
 

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

"JP" wrote:

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

On Nov 26, 10:56 am, Steve wrote:
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

Faster closing of large spreadsheet
 

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 wrote:

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

--

Dave Peterson

Faster closing of large spreadsheet
 

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.

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson

Faster closing of large spreadsheet
 

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

On Nov 26, 11:21*am, Steve wrote:
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