Macro to list data from several sheets in summary sheet

I need a macro to to the following:

I have several templates with exactly the same setup, and new sheets will be added. The data is structured so that, starting at Row 7 in each template sheet:


Sheet 2:

Country: AAA

Column A Column B Column C
Name A1 Number B1 Number C1
Name A2 Number B2 Number C2
Name A3 Number B3 Number C3

Sheet 3:

Country: BBB

Column A Column B Column C
Name B 1 Number B1 Number C1

Sheet 4:

Country: CCC

Column A Column B Column C
Name C1 Number B1 Number C1
Name C2 Number B2 Number C2


There can be a total of 9 items (Name 1...9) in each sheet, but the number can vary. I don't want to copy the blank rows.

I need the data to be summarized in a Summary sheet when runniong a macro, so that the setup looks like this:

Column A Column B Column C Column D
AAAA Name A1 Number B1 Number C1
AAAA Name A2 Number B2 Number C2
AAAA Name A3 Number B3 Number C3
BBBB Name B1 Number B1 Number C1
CCCC Name C1 Number B1 Number C1
CCCC Name C2 Number B2 Number C2


Any help would be greatly appreciated, as I'm really struggelig to get this working!

Thanks.

Thanks a lot - this seems to be doing what I need.

One question: I need to be able to process all sheets (except for the Summary Sheet). Can we do this in a way like this:

For I = 2 To Sheets.Count
A$ = Sheets(I).Name

...

I = I + 1
Next I

Also, the sheet names are likely to change from "Template 2,3,4" etc.

yes we can do this.but number of sheets have to be clearly defined