Copying data from multiple files into new one file.

**Algus** · 06-27-2013, 05:08 PM

Dear All,

I would need a help in creating macro which enables me to copy data from sheets of 3 files and paste them one below another in new file in one sheet.
All three sheets have the same column order from A till AA but the rows are variable and are changing on a monthly basis.
Summarizing, let's say my files are called 1,2,3 and the sheets are also called 1,2,3. They need to be copied to new file called 4 to one sheet called 4.
Truly apprecaite your support.

Best Regards,
Asia

**Leith Ross** · 06-27-2013, 06:46 PM

Hello Algus,

welcome to the Forum!

This macro will open the workbooks you specify and copy them into a new workbook.

The workbooks are assumed to located in the same folder and contain only a single sheet. You will need to change the workbook names (with extensions) and the path for your computer.

Copy and paste this code into a new VBA Module in your workbook. You can run the macro manually using the Macro Dialog. To call the dialog, press the keys ALT+F8.

Please Login or Register  to view this content.

**Algus** · 06-28-2013, 02:37 AM

Dear Leith,

Thanks a lot for reply. Since I am a totally new with VBA I came across some errors.
When I am running the macro it is saying "Object doesn't support this property or method" and the following code is marked in yellow :Set DstRng = DstWkb.Range("A1").
Apart from that I've forgotten to mention that the files with data to be copied will contain lots of sheets but the data should be taken only from one of them.
Also it would be useful to have them copied as a value, no formulas.
BTW ... are you able to recommend me some VBA trainings for dummies : ) just to have a basis and to learn by using ready codes.
Thanks a lot!
Algus

**Leith Ross** · 06-28-2013, 04:31 AM

Hello Algus,

I will check on the cause of the error and get that corrected.

Is the name of the sheet the same in each workbook?

At the the top of the first page of the Forum is a "sticky" post that has information about books and other learning resources for VBA. Try that first. the forum members have made a lot of contributions to the post.