You can download a workbook that contains the code in this article at Ron de Bruin's Web site.
Before you start: if your worksheets are identical, it's probably easier to create 3D-references (if you have one workbook) or External References (if you have multiple workbooks) to consolidate your data. However, the beauty of the Consolidate feature is that it can easily sum, count, average, etc this data by looking at the labels. Because our worksheets are not identical, we want Excel to sum cells that have the same labels. You can use Excel's Consolidate feature to consolidate your worksheets (located in one workbook or multiple workbooks) into one worksheet. If you check Create links to source data, Excel creates a link to your source data (your consolidated data will be updated if your source data changes) and creates an outline. Below you can find the workbooks of three districts. In the examples I use a values/formats copy but below the first example there is example code to copy only the values or everything to the RDBMerge Sheet. Enable Events = True End With End Sub Function Last Row(sh As Worksheet) On Error Resume Next Last Row = sh. Row On Error Go To 0 End Function Function Last Col(sh As Worksheet) On Error Resume Next Last Col = sh.
Important: The macro examples use the Last Row or Last Col function that you can find in the last section of this page.
After you have all the data on one worksheet, you can do things such as build a Pivot Table report based on your specific criteria or use the filter options in Excel 2007 to get the results you want.
(13 printed pages)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 consolidate the data in each worksheet into a summary worksheet.
Click in the Reference box, select the range A1: E4 in the district1 workbook, and click Add. Repeat step 4 for the district2 and district3 workbook. Check Top row, Left column and Create links to source data.
The examples will add a worksheet with the name RDBMerge Sheet to your workbook and will copy cellsfrom every worksheet in the Active Workbook to this worksheet.
Copy the macro(s) and function(s) in a standard module of your workbook. Cut Copy Mode = False End With End If Next Exit The Sub: Application.