Combine several sheets - Union, ADO, SQL, Consolidate, Formula?

[theta]

I have been working on a method of consolidating customer data. This data is used to create a summary table. Rather than have one 'live' table, I use source data to create a table, so that it remains flexible (and individual sheets can be exported to other workbooks). The summary table is then re-produced.

I am hoping to automate this process so that I can run a macro that will loop through all sheets and produce a summary table. The desired output would work as follows:

Worksheets (x10)
Heading in column A
Data in column B
These worksheets are all name 'Data_Sheet1' 'Data_Sheet2' etc (Data_ is the convention)

Summary
Heading along Row 1
Data consolidated below headings

I am not sure what is the best process for consolidating this data - union, ADO, SQL, consolidate, or a table made of formulas produced using a macro?

An example:

Data_Sheet1
Name...David
Age....24
Town...Reading

Data_Sheet2
Name...Peter
Age....30
Town...Cambrian

Data_Sheet3
Name...Michael
Age....40
Town...Brecon

Summary
Name......Age....Town
David.....24.....Reading
Peter.....30.....Cambrian
Michael...40.....Brecon

How can this be acheived? It will likely use arrays and loops to form a table, or an SQL/ADO style approach. Struggling with the code as very rusty.

Windows 7
Excel 2003
Excel 2007