Consolidating data from multiple workbooks into a separate workbook

Hello,

I currently have an issue which I'm struggling with and was wondering what's the best way of tackling this problem.

I have multiple workbooks that go by product and month that are continuously being updated with new information (so new rows are being added on a regular basis), e.g.

Product_A_July_2013.xlsx
Product_B_July_2013.xlsx
Product_C_July_2013.xlsx
Product_A_August_2013.xlsx
Product_B_August_2013.xlsx
Product_C_August_2013.xlsx

Essentially, what I need to be able to do is collate information from all those workbooks into a separate workbook. So, I'd like to have a pivot table which sources data from the above spreadsheets combined.

I'm thinking the best way of doing this is to find a way of automatically copying the worksheets from those workbooks into a single worksheet in a separate workbook. The only issue is that, the workbooks listed above are continuously being updated, i.e. new rows are constantly being added. Also, at the start of a new month, new spreadsheets are going to have to be included, e.g. September, October, November, etc.

I hope that all made sense. I'm still a beginner in VBA and so I'm not too sure if this is possible to do. Any advice or thoughts on how I could tackle this problem would be appreciated?

Thank you

Originally Posted by Naveed Raza

can you please attached sample excel workbook, its make very easy to resolve ur query

I've attached a copy of the spreadsheets just as an example of what I'm trying to achieve.

Consolidated.xlsx
Product_A_August_2013.xlsx
Product_B_August_2013.xlsx
Product_C_August_2013.xlsx

I would like the consolidated spreadsheet to update based on the Product spreadsheet.

My only concern is how I would manage this spreadsheet as the months go on, because I would have to include, September, October, November, etc in this spreadsheet.