Merge table columns and data from various excel workbooks into another

MS Excel Issues

I have a scenario where I will receive a number of monthly financial reports automatically from a credit risk system. Monthly this system will output 40 reports in excel format and place them on a directory on our servers.

Each month we are manually extracting columns of data out of these reports and compiling them into about 20 refined, visually presentable reports that will be used for publication purposes.

I would ideally like to eliminate the manual process involved in having to sift through these automatic reports to extract table columns, ideally creating a more simplified and automated system where excel automatically retrieves the column data and places it into an already formatted mother sheet, that then only needs to be published. Thus, reducing the hours spent on manual labor.

Ideally the scenario needs to work as illustrated in the attachment:

This system needs to be in place to repeat a monthly process indefinitely, and as time passes there will be some changes to the overall system that initially exports the data, where columns in the initial Excel exports may be rearranged or additional columns may be added (though, not to be included with in the final mother sheet)

Also, the number of results (rows) may vary from report to report and there for the depth of the final mother sheet table may vary, thus affecting the final ‘Total row in the mother sheet table.

Once the mother sheet is automatically populated and created with the new data, the tables are reference using the linked object option from within word for referenced placement; this also will eliminate the need to manually place the tables in word.

Are there some feature in Excel that would accommodate this and allow for the flexibility of future addition and changes to the initial source output?

I have tried the solution with a simple cell reference but it’s a rigid structure and doesn’t allow for any flexibility.

Thanks in advance all who have read through this and offer up ideas and solutions.

B.

I'm certainly no VB genius, but I recently had to move one column from 100 different spreadsheets into a 'motherhseet,' a similar task if I'm understanding correctly.

Since my VB was limited (a couple of classes in HS and college), I recorded a macro doing one "round" of [opening, copying, pasting, opening next file, coping, pasting] and then looked at the code. I extracted the portions I needed and created at macro so all I had to do was this:

Open all 100 excel files
click each window one at a time, press a macro shortcut key, click the next window, press the shortcut key, click the next window, etc....

What the macro did:
Opened the correct tab
copied the correct column
maximized the 'mothersheet'
pasted it in.

I had a lot of trouble figuring out how to make excel choose the "next available blank column" when pasting into the 'mothersheet' but eventually I got it.

Hope this gives you an idea