Copy Large Amt of Data from Closed Workbook

dallin01

Board Regular
Joined
Sep 16, 2009
Messages
61
I'm looking for faster VBA code that will copy abt 300,000 rows of data from a closed workbook to an active workbook. Currently I'm using code like the following which isn't that fast.

Workbooks.Open Filename:=strpath & totalfile
Sheets("PROD").Select
Sheets("PROD").Copy After:=Workbooks(OpNumfile).Sheets("LOS")
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

thanks.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
From the code fragment it appears, that you are first duplicating the sheet PROD into Workbooks(OpNumfile).
Then copying ALL the cells in Sheet named "PROD" and then pasting them back as values.

The second part (which is probably taking more time) can be also done with:
Cells.Value = Cells.Value
(this is still time intensive as the whole sheet is addressed)

Even better will be to use:
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
 
Upvote 0
Thankyou for your tips and observation - it works better. The most time consuming part on the code now is opening the files that contain the monthly updated data. Is there code that will allow me to copy/grab the updated data without opening the workbook?
 
Upvote 0
How big is the source workbook?
Reading data from a closed workbook is faster ONLY if you are reading a very small chunk from a very big file. John Walkenbach's method using XL4 macro appears best to me (it works both ways - closed/open workbook). Check here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=454

If however, you are reading a significant portion of the workbook, then there is no advantage as far as the speed is concerned (in fact the performance may degrade). You are better off opening the workbook, reading data and the closing it without save. If you set the Application.ScreenUpdating to False, you will not even notice that the workbook is opened and closed again. It is easier and there are no restrictions on how many cells you can return information from since the Excel4-macro creates links to the closed workbook. Check this: http://www.exceltip.com/st/Read_inf...orkbook_using_VBA_in_Microsoft_Excel/473.html
 
Upvote 0
Would you mind helping me understand this part of the code that excel tips is using. What does .Range("A10").Formula do? Is it looking for a formula? or is it just going to copy whats in cell A10? When I run the code I get a out of subscript range error. Thanks


With ThisWorkbook.Worksheets("destinationworksheet)
' read data from the source workbook
.Range("A10").Formula = wb.Worksheets("SourceSheetName").Range("A10").Formula
.Range("A11").Formula = wb.Worksheets("SourceSheetName").Range("A20").Formula
.Range("A12").Formula = wb.Worksheets("SourceSheetName").Range("A30").Formula
.Range("A13").Formula = wb.Worksheets("SourceSheetName").Range("A40").Formula
End With
 
Upvote 0
The With construct is copying the formulae from wb.Worksheets("SourceSheetName"). cells A10, A20, A30 and A40 to ThisWorkbook.Worksheets("destinationworksheet).Range("A10:A13").

You need to replace these statements with whatever you want to copy.

Subscript error comes if "SourceSheetName" or "destinationworksheet" are wrong (do not exist). Check the spelling and the Workbook references.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top