Excel VBA transpose specific range of data

Hi,

I have some data that I would like to transpose using VBA, but I have little knowledge of how to use VBA. I am using VBA from excel 2007

My data looks like this:

Name Name x Name y Name z
Code Code x Code y Code z
Date
Day 1 Price x1 Price y1 Price z1
Day 2 Price x2 Price y2 Price z2
Day 3 Price x3 Price y3 Price z3

I would like it to look like this:
Name Code Date Price
Namex Codex Day 1 Price x1
Namex Codex Day 2 Price x2
Namex Codex Day 3 Price x3
Namey Codey Day 1 Price y1
Namey Codey Day 2 Price y2
Namey Codey Day 3 Price y3
Namez Codez Day 1 Price z1
Namez Codez Day 2 Price z2
Namez Codez Day 3 Price z3

Each name code has several days of data (5218 days)

Can you please help me with the code or formula?

And I know excel has maximum of 65000?? rows?
My data columns exceed that.
After filling out the sheet is there a code for it to start on a new one?

Thankyou in advance,
StormingDays ^-^