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.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
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
 

dallin01

Board Regular
Joined
Sep 16, 2009
Messages
61
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?
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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
 

dallin01

Board Regular
Joined
Sep 16, 2009
Messages
61
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
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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.
 

dallin01

Board Regular
Joined
Sep 16, 2009
Messages
61
Thank you for taking the time to explain it to me. It is working very well!
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,143
Office Version
  1. 2007
Platform
  1. Windows
You are welcome!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,313
Members
414,052
Latest member
Dual Showman

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
Top