Consolidate / resort data code for excel

Hello,

I'm trying to write a macro for use in Excel that will take the data as shown in fig 1. and consolidate as in fig 2.
to another worksheet within the same workbook. Been trying without sucess for two days , please can anyone help. For each person named, there will only be one entry under the 'hours (A/B/C....)' columns for each week number.

Fig 1. (Sheet 1)

NAME......WEEK.....HOURS (A).....HOURS (B).....HOURS (C).....HOURS (D).....HOURS (E)
DAVID........1.............5.......................................3......................................2
KEVIN.........1.............3.......................................3......................................1
DAVID........1...................................7.......................................1..................
KEVIN.........2.............8....................2.........................................................3
DAVID........2..............6...................3.......................................1..................
KEVIN.........1..................................2.......................................4..................
DAVID........2......................................................5......................................2
KEVIN.........2......................................................9....................4.................


fig 2. (Sheet 2)

NAME......WEEK.....HOURS (A).....HOURS (B).....HOURS (C).....HOURS (D).....HOURS (E)
DAVID........1.............5..................7...................3..................1...................2
DAVID........2.............6..................3...................5..................1...................2
KEVIN.........1.............3..................2...................3..................6...................1
KEVIN.........2.............8..................2...................9..................4...................3