Copy files from multiple workbooks into one sheet

[DreyFox]

Hello all, I hope you are doing well. I'd like some help in making a macro that copies specific data from multiple workbooks simultaneously and copies them into one sheet. So:

1. I have these cells:

All of which can change values. For example the jumbo can be ZJ2 or ZJ3. Then the user can click:

Which when hit currently creates a new sheet named Master.

What I would like for help is, for Excel to find the file name of each of the values below the headers (ie, in the picture above, find the files ZJ2.xlsx, F12.xlsx, 65S.xlsx, 00.xlsx, 332.xlsx, 460.xlsx, and A.xlsx, B.xlsx, and C.xlsx) and copy them in the new sheet named Master.

The headers would need to be copied and there would need to be some offset cells between each copy. For example, say ZJ2 and F12 were copied, it would look like:

And so on.

Any help would be greatly appreciated. Thank you and have a wonderful day.

 

[richh]

Hi DreyFox,

May I ask,- are the workbooks formatted in the same way. In that I mean, are the column headers in each sheet you are looking to pull data from matching? If so, you may want to consider using a query. Here's a really great video on how to set one up. It will save you a ton of time.

 

[Marc L]

Hi !​

Any help would be greatly appreciated.

Any zip link on a files host website like Dropbox with at least a couple of source .xlsx files and the original workbook with the expected worksheet result​

- according to these source .xlsx files - saved as .xlsb binary format should obviously help to share a VBA procedure …​

 

[DreyFox]

Hi DreyFox,

May I ask,- are the workbooks formatted in the same way. In that I mean, are the column headers in each sheet you are looking to pull data from matching? If so, you may want to consider using a query. Here's a really great video on how to set one up. It will save you a ton of time.

Hi yes, the column headers will always be identical. I will take a look at Power Query. Thank you for your suggestion!