Need macro to open multiple file,copy and paste in a new spreadsheet

Hi guys,

I'm extremely new to macro but I'm thinking macro should be able to do this:-

I got 12 excel files, let's name it A.xlsx, B.xlsx, and so on, and each located at different directory:

For example:

C:\Desktop\New Folder 1\A.xlsx
C:\Desktop\New Folder 2\B.xlsx
C:\Desktop\New Folder 3\C.xlsx
C:\Desktop\New Folder 4\D.xlsx
C:\Desktop\New Folder 5\E.xlsx
C:\Desktop\New Folder 6\F.xlsx
C:\Desktop\New Folder 7\G.xlsx
C:\Desktop\New Folder 8\H.xlsx
C:\Desktop\New Folder 9\I.xlsx
C:\Desktop\New Folder 10\J.xlsx
C:\Desktop\New Folder 11\K.xlsx
C:\Desktop\New Folder 12\L.xlsx

All the excel files, from A.xlsx to L.xlsx, I want to copy cell A397:M403 and paste into a spreadsheet

Note that A397:L397 are merged cell, the rest are no-merge cell

For file A.xlsx to F.xlsx, I want it to be copy and paste into new spreadsheet on the left hand, and for file G.xlsx to L.xlsx, I want it to be copy and paste on the right hand side, so that I can view side by side as per the draft below:-

A.xlsx G.xlsx
A397:M403 A397:M403

B.xlsx H.xlsx
A397:M403 A397:M403

C.xlsx I.xlsx
A397:M403 A397:M403

D.xlsx J.xlsx
A397:M403 A397:M403

E.xlsx K.xlsx
A397:M403 A397:M403

F.xlsx L.xlsx
A397:M403 A397:M403

Currently it's too manual to open one by one and copy paste in a monthly basis
And for some reason, when i paste in formulae, next month when I open the file, the formulae are in chaos before I replace the formulae with new file (due to new directory...)

Hope to hear soon from macro-expert here....

Let me know if there's enquiry..

Thanks

regards
cks1026

Hi natefarm,

First of all, thank you for follow up my issue.

The format from this macro is exactly what I wanted !!
But...there's one small issue...

All the spreadsheet from A.xlsx to L.xlsx range from A397:M403 are with formulae such as SUM, AVERAGE, etc
After run the macro, all the cell are returning #REF!

Is there any chance that you can adjust the macro code by linking every cell to the relevant spreadsheet, so that when the relevant spreadsheet had adjustment, re-run the macro will capture the latest adjustment in this MultiFileProcess.xlsm

If this is not possible, then paste all in value with the same format from relevant spreadsheet will do.

Thanks

Regards
cks1026

Hi natefarm,

For some reason, after you updated the attachment to copy values, couple of issues occurred.

1.) There's run time error '1004' stated PasteSpecial method of Range class failed.
When I click debug, it points to this row ---- > ThisWorkbook.Sheets("Results").Cells(ToRow + 1, ToCol).PasteSpecial (xlPasteValues)

2.) the format of the 12 sources excel file are not being copied and paste into similar format

Please advice.

Thanks

Regards
cks1026

Can I just check with you if these code works when copy the merge cell and paste special in merge cell format as well?

yes, that's what I'm asking.... there are merged cell I'm copying to new destination.....