Consolidating data in excel worksheets
If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.
Ron de Bruin, Microsoft Office Excel MVPFrank Rice, Microsoft Corporation August 2008Applies to: Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002, Microsoft Excel 2000Contents or merge the data in each workbook into a master workbook.
If a few simple rules are followed when laying out your data, then producing additional reports from that data, and using it for different purposes, becomes simple, instead of the nightmare it is for many users.
These rules apply to any lists of data, be it monthly financial information, transactional data (such as lists of sales, purchases, payments or receipts), customer or supplier lists.
The code then does the same test for the rows in the source range.
If this is true, then this workbook is skipped and the code moves to the next workbook.When someone comes to me with a problem in an existing spreadsheet, the problem is invariably in the layout of the data.The spreadsheet is built for one purpose and works OK for that until something slightly different is required and it proves almost impossible to get the report that's needed.Additionally, you will see how to retrieve data by using a filter.And finally, you will see a utility that pulls all of these techniques together and more in one location.