For example, if you have a table with the number of sold items from each of your regional stores, you can use data consolidation to roll up these figures into a single report.
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.
The examples described in this article add the data from multiple workbooks to a summary workbook.
The different procedures demonstrate techniques for pasting the data by row or by column.
This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet. Additional things that may be used to enhance this code 1. Select last Row = Last Row In One Column(str Start Cell Col Name) Cells(last Row 1, 1). Paste Special xl Paste Values, xl Paste Special Operation None Application. We start by assigning the workbook where we want to consolidate the date to the variable current WB by using the statement: After this a looping construct has been used to go through all the inputs provided one by one and open the workbooks, it has been assumed these workbooks to contain on the data that we need to copy hence I did not specify the source sheet name, however this can be easily added to this code to add more functionality. The current code will display a message box when it is not able to open any file and will stop. The data copy operation is not complete.” Exit Sub End Sub We have used the Workbook object to accomplish this task and also the Error handler to trap any errors that may come in case any file is missing. Select Loop Exit Sub Err H: Msg Box “It seems some file was missing.