Copy files from multiple workbooks into one sheet

DreyFox

Board Regular
Joined
Nov 25, 2020
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
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:
1627306392663.png

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

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:
1627306677388.png
And so on.

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

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

richh

Board Regular
Joined
Jun 24, 2007
Messages
243
Office Version
  1. 365
  2. 2016
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,633
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Nov 25, 2020
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,144,695
Messages
5,725,807
Members
422,642
Latest member
praveenlal

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