Automatically copying data from multiple spreadsheets into one master spreadsheet

Hi,

I am developing some reports in excel and was just wondering if anyone can help me ease out the very time consuming activity I am doing.

I have multiple spreadsheets which has simillar data in them filled in by multiple teams. The file names for these are also simillar with only the team name changing (e.g. PSI_2009_Team 1.xls). I need to copy data from each of these sheets automatically to a consolidated master spreadsheet. Then I can play with the date to do my reporting.

Attached are sample sheets to work on a solution.

I need data from <PSI_2009_Team1.xls>, <Team2.xls> and <Team3.xls> to be copied to <Report.xls> one below the other.

Also, I need to be able to run the macro in the consolidated report sheet whenever a new PSI_2009 file is generated to add that data into my consolidated sheet.

Can someone kindly help me with this, I am sure someone out there knows how to get this done. I need to work on this as soon as possible

Are they in one workbook (three tbs) or three stand alone workbooks?

Hi Adhide,

These are all seperate workbooks. I have attached samples of the same in my original thread.

Also, they are stored in the same location.

Basically, I need data to be copied from each of these sheets to one consolidated sheet automatically by running a macro. And all sheets at once, if possible.

Thank you in advance.