Solved: Layout of Monthly Data needs to change

[debauch]

Hello,

Currently we recieve a forecast each month, and there is several tabs in it. It is not in a format that we can load into our tables, so we need to change it to be vertical rather than horizontal.

I've attached a sample of how it comes, and how we'd ideally like to see it.
I've tried to do it through formula's but I cannot seem to come up with something that can will include the date.

Can someone please check out my sample attachment and share some insight on how this might be achieved?

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim iLastRow As Long
Dim iLastCol As Long
Dim sh As Worksheet
Dim thisSh As Worksheet
Dim iRow As Long

Set thisSh = ActiveSheet
With thisSh

Set sh = Worksheets.Add
sh.Name = "Summary"
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
iRow = 1
For j = 2 To iLastCol Step 3
.Range("A2").Resize(iLastRow - 1).Copy sh.Range("A" & iRow)
.Cells(2, j).Resize(iLastRow - 1, 3).Copy sh.Range("B" & iRow)
sh.Range("E" & iRow).Resize(iLastRow - 1).Value = .Cells(1, j)
iRow = iRow + iLastRow - 1
Next j
sh.Columns(5).NumberFormat = thisSh.Range("B1").NumberFormat

End With

End Sub
[/vba]

[debauch]

Holy wicked!! That was awsome. I have not had a chance to fully understand your code , but this will work perfect!!

You rock XLD. Hopefully I will understand it enough to modify a few other parts into it. Thanks!