First my apologies for the style, I got started on CBM Pet basic and haven't lost the spaghetti coding bug yet.

This works on your current data set and should work no matter how you expand the cols and rows, although I didn't test it fully.

A few points

1. the original data should be on a worksheet called "ORIGINAL" and the output sheet created as "output"...basically just as they are now.
2. The header "Blank" must be in the relative place as it appears in your original.
3. To achieve your exact result you should delete your unwanted cols in the output like "Area" or whatever.
That's about it.


Sub trans()



Dim col As Integer

Dim origrow As Integer

Dim outrow As Integer

Dim datcol As Integer



outrow = 1



label1:

col = col + 1

If Worksheets("ORIGINAL").Cells(1, col) = "Blank" Then GoTo label2

datcol = col

Worksheets("Output").Cells(1, col + 1) = Worksheets("ORIGINAL").Cells(1, col)

GoTo label1

label2:

col = col + 1

origrow = 1

label3:

outrow = outrow + 1

origrow = origrow + 1

Worksheets("Output").Cells(outrow, 1) = Worksheets("ORIGINAL").Cells(1, col)

For x = 1 To (datcol)

Worksheets("Output").Cells(outrow, x + 1) = Worksheets("ORIGINAL").Cells(origrow, x)



Next x

Worksheets("Output").Cells(outrow, x + 1) = Worksheets("ORIGINAL").Cells(origrow, col)

If Worksheets("ORIGINAL").Cells(origrow + 1, col) <> 0 Then GoTo label3

If Worksheets("ORIGINAL").Cells(1, col + 1) <> 0 Then GoTo label2



End Sub
Brief explanation how it works.
The macro scans along the header row writing all it finds into the output until it hits "Blank"
It will then take the value of the next col (Date) and place it in col A row1 and populate the rest of the row.
It will then check to see if the next row has data, etc until cell is empty, it will then check to see if the next date header isn't empty and carry on.

Hope it does the trick, but I'm sure it wouldn't need much tinkering to sort it.
best of luck

col