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

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

below macro shoud work; provided that
- worksheet names start with "Data_"
- headers are in A1:A3
- data cells are B1:B3

if not, you should amend the code.

Code:

---

Sub vbax_54050_consolidate_sheets()

    Dim ws As Worksheet
    Dim i As Long
   
    With Worksheets("Summary")
        .Cells.Clear
        .Range("A1:C1").Value = Array("Name", "Age", "Town")
        i = 2
        For Each ws In Worksheets
            If Left(ws.Name, 5) = "Data_" Then
                .Range("A" & i & ":C" & i).Value = Application.Transpose(ws.Range("B1:B3").Value)
                i = i + 1
            End If
        Next ws
    End With

End Sub

---

worksheet names start with "Data_"
headers in A1:A3
data in B1:B3

Code:

---

Sub M_snb()
  with createobject("scripting.dictionary")
    .item(.count)=array("name","age","town")

    for each sh in sheets
      if sh.name<>"summary" then .item(.count)=application.transpose(sh.cells(1,2).resize(3))
    next

    sheets("summary").cells(1).resize(.count,3)=application.index(.items,0,0)
  end with
End Sub

---