Copy/paste the code below into a macro code module.
It will make a new Excel sheet and copy data into it.
I assume that the data is in separate cells.

'-------------------------------------------------------
Sub test()
Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Dim FromRow As Long
Dim ToRow As Long
Dim FromCol As Integer
Dim ClaimNumber
'------------------------------
Set FromSheet = ActiveSheet
Set ToSheet = Worksheets.Add
FromRow = 2
ToRow = 2
'-----------------------------
While FromSheet.Cells(FromRow, 1) < ""
ClaimNumber = FromSheet.Cells(FromRow, 1).Value
FromCol = 4 ' FIRST COLUMN WITH CODE NUMBER
While FromSheet.Cells(FromRow, FromCol).Value < ""
ToSheet.Cells(ToRow, 1).Value = ClaimNumber
ToSheet.Cells(ToRow, 2).Value = FromSheet.Cells(FromRow
2).Value
ToSheet.Cells(ToRow, 3).Value = FromSheet.Cells(FromRow
3).Value
ToSheet.Cells(ToRow, 4).Value = FromSheet.Cells(FromRow
FromCol).Value
FromCol = FromCol + 1
ToRow = ToRow + 1
Wend
FromRow = FromRow + 1
Wend
End Sub
'--------------------------------------------------------

--
Message posted from http://www.ExcelForum.com