Help with macro: How to copy from different sheets and paste traspose in a different file

[excedrin]

Hi everyone,
I am new to writing macros and I have searched this topic but I can't find something that works.

I have data in an excel file that is separated in sheets. Each sheet contains country names starting in A2:A201 and numbers for each country along the columns that start from year 2009 - 2050 (D1:AS1)

I have 15 sheets in the same excel file that are in the same format as described above.

Here is a simple example of what each sheet looks like:

'Sheet 1'

Country Name	2009	2010	2011	2012	2013
Country 1	1	1	1	1	1
Country 2	2	2	2	2	2
Country 3	3	3	3	3	3

<tbody>
</tbody>

'Sheet 2'

Country Name	2009	2010	2011	2012	2013
Country 1	5	5	5	5	5
Country 2	6	6	6	6	6
Country 3	7	7	7	7	7

<tbody>
</tbody>

'Sheet 3'

Country Name	2009	2010	2011	2012	2013
Country 1	9	9	9	9	9
Country 2	10	10	10	10	10
Country 3	11	11	11	11	11

<tbody>
</tbody>

PROBLEM:
I need to copy and paste transpose the info from these sheets into a new excel file where the country name is located in C18, the year pasted on F18 and each tab. Each sheet is being transposed by country and year also (starting in G18 - U18) as shown in the example below:

Country Name	year	Sheet 1	Sheet 2	Sheet 3
Country 1	2009	1	5	9
Country 1	2010	1	5	9
Country 1	2011	1	5	9
Country 1	2012	1	5	9
Country 1	2013	1	5	9
Country 2	2009	2	6	10
Country 2	2010	2	6	10
Country 2	2011	2	6	10
Country 2	2012	2	6	10
Country 2	2013	2	6	10
Country 3	2009	3	7	11
Country 3	2010	3	7	11
Country 3	2011	3	7	11
Country 3	2012	3	7	11
Country 3	2013	3	7	11

<tbody>
</tbody>

Is it possible to write such macro? I need to do this for many files and it take a while to do it by hand which also introduces human error.

Any help would be greatly appreciated!

 

[excedrin]

Help!!! Anyone???