Para o Excel 2010 ou 2013, eu usaria o suplemento do Power Query para isso. No Excel 2016, o Power Query é incorporado à faixa de opções Dados na seção "Obter e transformar".
O Power Query pode começar a partir de uma pasta contendo arquivos do Excel. Ele retorna isso como uma tabela listando os arquivos, que você pode filtrar conforme necessário. A técnica principal nesse ponto é adicionar uma coluna com a fórmula:
Excel.Workbook ( [Content] )
Em seguida, você pode expandir essa coluna para obter uma lista das tabelas e intervalos nomeados em cada arquivo. Continue expandindo e você chegará aos dados da linha em cada tabela. Fornecer os arquivos são consistentes, você deve ser capaz de filtrar apenas os dados desejados. O resultado pode ser escrito em uma nova tabela do Excel.
Por padrão, isso fará uma atualização completa toda vez que você informar Refresh your Query - esse é geralmente o método mais confiável. Se você precisar de algo mais complicado, você pode ler os dados "Master" e combiná-los usando um Merge, para eliminar as linhas já carregadas.