help! create master workbook
 

I am working on consolidating several project plans into one. Each
department has a Excel workbook project plan. Each plan is stored on the
same SharePoint site, in a separate Document Library for each workbook. Each
project plan is identical in structure. I did not design the plan structure,
but I need to create a master workbook, also on the same SharePoint site,
that pulls all records from each department's project plan onto one. Each
project plan has identical column structure but varying numbers of rows based
on their tasks. I would like to write code to create an updated master
project plan each time a macro is run. I'm not sure of the best way to do
this. Is there a simple way to do this by linking the workbooks? I would
like to have the end users need only to open the master workbook and be able
to leave the other workbooks closed, if possible. There are about 20
workbooks.