You might be able to use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts:

The below steps will consolidate ranges named rngNameAmt1 (which, in my
example, contain 2 columns: Name and Amount) from 4 Excel files into the one
workbook.

Assumptions:
-The data in each wkbk is structured like a table:
---Col headings (PmtAmt, DatePaid, etc),

---Col Headings different ranges can be in any order, but must contain
similar data.

-The data in each wkbk is in named ranges.
---You may use the same range name in different wkbks.

Note: At various points in the below process, MS Query may display warnings
about it's ability to show the query
....ignore them and proceed.

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)DataImport External DataNew Database Query
Databases: Excel Files

-Browse to one of the files, pick the data range and columns to import.
---Accept defaults until the next step.

-At the last screen select the View Data/Edit the Query option.

-Click the [SQL] button

-Replace the displayed SQL code with an adapted version of this:

SELECT Name, Amount FROM `C:\Analysis\Wkbk1`.rngNameAmt1 1stWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk2`.rngNameAmt1 2ndWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk3`.rngNameAmt1 3rdWkbk
UNION ALL SELECT Name, Amount FROM `C:\Analysis\Wkbk4`.rngNameAmt1 4thWkbk

Return the data to Excel.

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

You can edit the query at any time to add/remove data sources and/or fields.

Is that something you can work with? If you have questions...post them.

Regards,
Ron