MVP Ron de Bruin provides a number of samples to merge data from multiple worksheets into one summary worksheet.
Here is the original:
Consolidating Data from Multiple Worksheets into a Summary Worksheet in Excel 2007
Consolidating Data from Multiple Worksheets into a Summary Worksheet in Excel 2007
Filed Under: Excel, Samples Tagged With: based-on-your, criteria-or-use, data, Excel, Featured, filter-options, results, selection, setting, specific, such-as-build, version 2003, VSTO, your-code, your-specific