A automação do Excel Ron de Bruin tem muitas páginas com Excel gratuito Excel VBA Tips e Excel Add-ins.
Eu acho que RDBMerge, o Excel Merge Add-in para Excel para Windows pode ser exatamente o que você precisa .
RDBMerge is a user friendly way to Merge Data from Multiple Excel Workbooks, csv and xml files into a Summary Workbook.
Install the RDBMerge utility
1) Download the correct version and extract it to a local directory.
2) Copy RDBMerge.xla(m) to a unprotected directory on your system. Tip: Use one folder for all your add-ins (easy to backup your add-ins this way)
3) Start Excel and open a workbook.
Excel 97-2003
Click Tools, click Add-Ins, use "Browse" to go to the add-in and then click OK. Verify RDBMerge is checked in the add-in list and then click OK.
Excel 2007-2013
2007: Click the Microsoft Office Button, click Excel Options, click the Add-Ins tab.
2010-2013: Click on File, click on Options, click the Add-ins tab. In the Manage drop-down, choose Excel Add-ins, and click Go. Use “Browse” to go to the add-in and then click on OK. Verify RDBMerge is checked in the add-in list and then click OK.
Há instruções passo-a-passo detalhadas e links de download no restante do artigo.
Você também pode pesquisar em seu site por "mesclar planilhas" para outras possibilidades.