Annette wrote:
How can I transpose the above other than selecting each record individually
and paste special with transpose. I have thousands of records and would
like to just run a macro to do the job.

thanks!

I had a spreadsheet that needed to be reformated from rows to columns,
but the order I needed to have it put in was not the order the data was
in. Here is the macro I wrote for this. In the macro I have it
skipping rows in the formating to line up with a template I was using
ex (irow + 10, icol) these can be changed to be in sequence.


Sub FORMAT()
'

Dim irow As Integer
Dim icol As Integer
Dim i As Integer

Selection.Formula = Selection.Value
finalrow = Range("A9999").End(xlUp).Row

'paste the data starting 10 rows from the bottom row
irow = finalrow + 10
icol = 5
For i = 1 To finalrow

'colunm O plus row number the "+ 1" makes it go to the next row
in the loop
'then I tell it where to put the data
Range("O" & i + 1).Copy Destination:=Cells(irow, icol)
Range("A" & i + 1).Copy Destination:=Cells(irow + 1, icol)
Range("M" & i + 1).Copy Destination:=Cells(irow + 2, icol)
Range("N" & i + 1).Copy Destination:=Cells(irow + 3, icol)
Range("H" & i + 1).Copy Destination:=Cells(irow + 10, icol)
Range("E" & i + 1).Copy Destination:=Cells(irow + 11, icol)
Range("K" & i + 1).Copy Destination:=Cells(irow + 13, icol)
Range("Q" & i + 1).Copy Destination:=Cells(irow + 14, icol)
Range("W" & i + 1).Copy Destination:=Cells(irow + 15, icol)
Range("X" & i + 1).Copy Destination:=Cells(irow + 16, icol)
Range("AB" & i + 1).Copy Destination:=Cells(irow + 19, icol)
Range("AC" & i + 1).Copy Destination:=Cells(irow + 20, icol)
Range("AD" & i + 1).Copy Destination:=Cells(irow + 21, icol)
Range("AE" & i + 1).Copy Destination:=Cells(irow + 24, icol)
Range("AF" & i + 1).Copy Destination:=Cells(irow + 25, icol)
Range("AG" & i + 1).Copy Destination:=Cells(irow + 26, icol)
Range("T" & i + 1).Copy Destination:=Cells(irow + 28, icol)
Range("AL" & i + 1).Copy Destination:=Cells(irow + 30, icol)
Range("AM" & i + 1).Copy Destination:=Cells(irow + 31, icol)
Range("AN" & i + 1).Copy Destination:=Cells(irow + 32, icol)

'puts next rows data into a new column and skips a column
icol = icol + 2
Next i
End Sub