Reformatting data for Pivot Tables

[Hassled1]

As part of resource management I have a lot of data in tables (Excle 2007) for a particular role. I'm now looking to add some additional reporting to this and would like to use a pivottable however my source table is not in a suitable form for a pivot table.

My data is in the following form
Column headers:
Role, Project, Rate, Week1, week 2, Week 3 etc to Week 52
The first three columns are fairly self explanatory. In the column for each week is the total effort required for that role.

I would like to copy this data from this sheet to a new sheet in the following format
Headings:
Role, Project, Rate, Date, Effort
The date field will contain the header value of Week 1 etc (stored as a date), the effort is the value under that column in the orginal table.

An example is
Role, Project, Rate, 03/06/2012, 10/06/2012, 17/06/2012
Team1, Proj1, 300, 2, 3, 4
Team2, Proj 1. 350, 1.5, 2.5

would map to
Role, Project, Rate, Date, effort
Team1, Proj1, 300, 03/06/2011, 2
Team1, Proj1, 300, 10/06/2011, 2
Team1, Proj1, 300, 17/06/2011, 2
Team2, Proj1, 300, 03/06/2011, 1.5
Team2, Proj1, 300, 03/06/2011, 2.5


I'm struggling to set this up using vba, any help would be appreciated.

Thanks