Sub aParseMacro1()
Dim vNum, vDate, vName, vTask, vVal
Dim shtSrc As Worksheet, shtTarg As Worksheet
Dim vPrevNum, vDte
Dim colDtes As New Collection
Dim colVals As New Collection
Dim iCols As Integer, i As Integer
Set shtSrc = ActiveSheet
Sheets.Add
Set shtTarg = ActiveSheet
shtSrc.Activate
Range("c1").Select
While LCase(ActiveCell.Value) <> "sum"
iCols = iCols + 1
vDte = ActiveCell.Value
colDtes.Add vDte
ActiveCell.Offset(0, 1).Select 'next column
Wend
Range("B2").Select
While ActiveCell.Value <> ""
If Not IsEmpty(ActiveCell.Offset(0, -1).Value) Then vNum = ActiveCell.Offset(0, -1).Value
If vPrevNum <> vNum Then
vName = ActiveCell.Value
Else
vTask = ActiveCell.Value
'collect vals
Set colVals = New Collection
For i = 1 To iCols
colVals.Add ActiveCell.Offset(0, i).Value
Next
'post data
shtTarg.Activate
For i = 1 To iCols
ActiveCell.Offset(0, 0).Value = vName
ActiveCell.Offset(0, 1).Value = colDtes(i)
ActiveCell.Offset(0, 2).Value = vTask
ActiveCell.Offset(0, 3).Value = colVals(i)
ActiveCell.Offset(1, 0).Select 'next row
Next
shtSrc.Activate
End If
vPrevNum = vNum
ActiveCell.Offset(1, 0).Select 'next row
Wend
shtTarg.Activate
Set shtSrc = Nothing
Set shtTarg = Nothing
Set colDtes = Nothing
End Sub