Extract data – Multiple Files – to Single File – in Excel

MasterFile.xlsx
MultipleFilesData.xlsx
Hi all,

I need help with appending data from several excel files with the same “data titles” into a single Excel MasterFile. I know there is lot of help/codes out there for simple merge/append, but this one has to extract relevant data and match column/row titles, (some Titles in some files may not be in the same order).

I want all this data to be brought in a single row in the new Excel MasterFile under appropriate Titles.
So data from each data file will be arranged under appropriate Column Titles without error.

Location where files saved are:
Master excel file is saved in Folder C:\Users\Nostro\Documents\MasterFile
Multiple excel files are saved in Folder C:\Users\Nostro\Documents\MultipleFiles

I am attaching the MasterFile (where data needs to be appended); and one sample file (I have more than 200) from where data needs to be extracted.
I would reapply appreciate help on vba coding or any other appropriate method.

Thanks.

One more detail, in the "MultipleFilesData" file, cell A1 has the fish number (each of the 200 plus files has unique fish number in cell A1).
This info needs to go in cell A2 in the MasterFile. So, it will be in A2, A3, A4, A5.... as data from consecutive files is appended to the MasterFile.

Originally Posted by mgs73

This should do it for you - good luck!

Please Login or Register  to view this content.

Thanks mgs73 for the code, I ran it, and it ran good for all the files in the folder. But there is one issue
From the MultipleFiles, it only extracted data values from Col B. Data from Col C, and Col D was not appended to the MasterData file. Is there anything I need to add (missing code)?
Thanks again.

Oops...

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Originally Posted by mgs73

Oops...

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thanks for the fast reply.
So the code is still partially working - it is importing from Col B & Col C, but not col D.
Also, this time the values imported from each file do not line up in a single row. They go one row below the next in a diagonal fashion.
Hope you can see what I am talking about by inserting the code in the file I sent above

Oops again! The r = r + 1 should've gone after 'Next' before the workbook is closed.

Thanks for the edits. So I'm not sure if I'm doing this right now, but am getting the below Debug message:
Set sh = ThisWorkbook.Worksheets("MasterData")

So, I open the MasterData excel file where I need the data imported in. These are the steps I am following after opening the file.
Alt F11; right-click on Sheet1 (under VBAProject (MasterData); Insert; Module. Then paste your code; Ctrl S; select No, and Save as Macro-Enabled File; Then Alt X to close VBA. Then I Run the Macro and the above underlined error pops up.
VBA code I am using is below (given by you):

Please Login or Register  to view this content.

What error message do you get? Can you upload the workbook?

ok my bad, your code worked great, I just forgot to rename Sheet1 to MasterData, and then everything was good.

We are 75% there. There were still few columns (below) that did not get their data populated:

Inc_1_Length(mm) Inc_2_Length(mm) Inc_3_Length(mm) Inc_4_Length(mm) Inc_EdgeA_Length(mm) Inc_1b_Length(mm) Inc_2b_Length(mm) Inc_3b_Length(mm) Inc_4b_Length(mm) Inc_EdgeB_Length(mm)

Length data here for some reason did not pull up from any of the files, I wonder if something is missing again

mgs73, would the code be any simpler/easier if I put all the files and the MasterData file in the same folder for convenience?

Here is the workbook, and a couple of the multiple files I am extracting the data from, with your code.
MasterData.xlsm
GB_40X_109.xlsx
GB_40X_110.xlsx

I ran the code here, and it worked ok. Maybe I've structured the directories different to you?

C:\Temp\MasterFile\MasterData.xlsm
C:\Temp\MultipleFiles\GB_40X_109.xlsm
C:\Temp\MultipleFiles\GB_40X_110.xlsm

Originally Posted by mgs73

I ran the code here, and it worked ok. Maybe I've structured the directories different to you?

C:\Temp\MasterFile\MasterData.xlsm
C:\Temp\MultipleFiles\GB_40X_109.xlsm
C:\Temp\MultipleFiles\GB_40X_110.xlsm

Were you able to get the last columns to populate with data?

If the sheet names in the folder is common...
try

Please Login or Register  to view this content.

Formula adjusted for the heading with multiple underscores.

Please Login or Register  to view this content.