Excel 2007 : Macro creation - transposing columns to rows

I'm trying to build a freight calculator and am relying on information generated by SAP. For each route, I have a table in the following format as per the example table below:

Route	Weight Break	Rate
Route 1	5,000	$200
Route 1	10,000	$150
Route 1	15,000	$100

I would like to change this to the following format (ie. so each route is only 1 row, and there is a different column for each weight break:

Route	WB_5,000	WB_10,000	WB_15,000
Route 1	$200	$150	$100

The paste special --> transpose can do this, but I have hundreds of these I need to convert so I'm trying to create a macro to do this automatically. And to make things more interesting, some of the data differs from this standard format, so I'm trying to create something where I can use a hotkey to run the macro on certain rows to perform this action.

However I'm not really sure where to start, I'm hoping someone can give me an indication as to what approach I should be taking here...

Hi the transpose formula may be a good place to start - it's an array formula, so you need to select the right number of rows and columns, type in a formula something like this, and press control+shift+enter (the formula should appear in curley brackets in the fomula bar)

Please Login or Register  to view this content.

or, using a macro to insert the formula array

Please Login or Register  to view this content.

from these you can convert to values, copy the formula, repeat the macro, or do what you need to transpose other arrays

Thanks for the help, I'm pretty sure this will work for what I'm trying to achieve, I'll be giving it a crack today

Thanks again!