Automation using VBA

Hi,



I am having an Excel sheet of the following format:



Item1 Item2 Item3 QTY Date

A1 B1 C1 200 2-Jun-08
A1 B1 C2 300 9-Jun-08
A2 B2 C1 400 16-Jun-08
A2 B2 C2 500 23-Jun-08





Using VBA, I want to automatically convert this data in to the following format:




2-Jun-08 9-Jun-08 16-Jun-08 23-Jun-08
Item1 Item2 Item3

A1 B1 C1 200
A1 B1 c2 300
A2 B2 C1 400
A2 B2 C2 500




I could have recorded a macro, creating a tranpose of the columns and done some formatting.



The challenge is that number of columns (ItemX ) is dynamic.



So is the number of records which keep changing every day.



Can someone help me how to go about achieving this ?





Thanks,

kingfisher