This might be an option for you if:
a)You are familiar with MS Query, and
b)Are OK with using (or learning) some basic SQL code:

Assumptions:
-The data in each wkbk is in named ranges

1)Select the cell where you want the consolidated data to start
2)DataImport External DataNew Database Query
Databases: Excel Files
-Select one of the files, pick the data range and columns to import and any
criteria.
-Select Edit the Query
-Click the SQL button
-Replace the displayed SQL code with an adapted version of this:
SELECT Name, Amount From (SELECT Name, Amount
FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk UNION SELECT Name, Amount
FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk UNION SELECT Name, Amount
FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk UNION SELECT Name, Amount
FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk)

Return the data to Excel.

The above will consolidate ranges named rngNameAmt1 (which contain 2
columns: Name and Amount) from 4 Excel files into the one workbook

One that is done....all you need to do to get the latest data is click in
the data range then DaveRefresh Data.

Is that something you could play with?
--
Regards,
Ron