Hello,

Please could someone help me?
I need to write a macro that will open up a folder containing over 3000 workbooks, copy the data from each workbook and paste the data into a new workbook.

How can I get the macro to copy the data from the first workbook into a new workbook and then copy the data from the next workbook and paste it below the data that was just copied across?

Is there a loop that can be used in order to do this?

Any help would be very much appreciated.

Thank you

This would be a failry complex VBA module to loop through the directory, use Office Automation to open and copy the contents, then paste it into the current sheet. The actual positioning the cursor for the paste is one of the easiest pieces.

However, my Excel VBA skills are not up to this task. So if no one else answers I would suggest posting this at utteraccess.com. There are a few Excel MVPs who monitor the Excel board there.

Do the 3000 other workbooks change names or are they always the same named documents? If so, you could do this yourself pretty easily.


ChDir "C:\Documents and Settings\Jerry2\My Documents\ExcelDocs"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Jerry2\My Documents\ExcelDocs\WORKBOOK1.xls"
Range("B2:C4").Select
Selection.Copy
Windows("MAINWORKBOOK.xls").Activate
Range("A6").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("WORKBOOK1.xls").Activate
ActiveWindow.Close
(now repeat this for the next workbook and range.)

Down and dirty but would work. There are some VB tricks to probably shorten the whole macro, power tricks.

Another way is to simply reference in the MAINWORKBOOK cells that refer to data in the other sheets. If that data being copied over is in the same place all the time, this is probably the easiest method.

This is called LINKING and the help files in Excel show the easiest examples:

Link
In A1
=('C:\Reports\[Budget.xls]Annual'!C10)
In B1
=('C:\Reports\[Budget.xls]Annual'!C11)

... etc.

That format includes the full path, the doc name in [brackets],the name of the sheet in the workbook, and then the cell reference.