copy data from multiple files in a folder with their respective filenames into 1 actively open sheet

[Kvt]

Hi Champs

Fairly new to VBA coding and need help please.

I need to copy data from multiple files in a folder with their respective filenames into 1 actively open sheet.

Example
A File from Adam (adam.xls) has data
ColumnA ColumnB columnC
Cookie sugar flour
Pizza sauce cheese

Another File from Alex (alex.xls) has data
ColumnA ColumnB columnC
Cake Chocolate raisins
Bread flour water

I want a code that will combine the above 2 file data kept in a folder with their file names.

Expected output
ColumnA ColumnB columnC columnd
Cookie sugar flour Adam.xls
Pizza sauce cheese Adam.xls
Cake Chocolate raisins Alex.xls
Bread flour water alex.xls

This way I would know who sent what data.

Possible? Please advise.

Thanks heaps

 

[richh]

Hi Kvt,

Are the files you are looking to aggregate in the same layout (column headers match, data is stored in the same manner)

If so, you may want to consider using Power Query. Here's a great video on how to go about setting one up.