Move data to column based on startdate

[J]

Hi,
I'm using Excel 2007 and I'm trying to figure out how to write a macro that
will copy input data from one table (which is not date dependent) to a new
table with dates in the first row. I would like the data to be copied
starting at the column which matches the startdate. I have figured out a
complicated way to do this using the OFFSET function in the spreadsheet but
its a mess. I'd prefer to do it in VBA to keep it a little tidier. I also
want to avoid absolute cell references. Here's an example:

Input Table
Project Startdate $Period1 $Period2 $Period3
ProjectA 2010 $1 $2 $3
ProjectB 2012 $10 $12 $14
ProjectC 2011 $3 $4 $5

I need a macro that will transform the above table into the following:
Project 2010 2011 2012 2013 2014
Project A $1 $2 $3 $0 $0
Project B $0 $0 $10 $12 $14
Project C $0 $3 $4 $5 $0

Any help would be greatly appreciated!

 

[Bob Phillips]

Public Sub ProcessData()
Dim sh As Worksheet
Dim LastRow As Long
Dim NumYears As Long
Dim i As Long
Dim j As Long

Set sh = Worksheets("Sheet2")
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

sh.Range("A1").Value = "Project"
NumYears = Application.Max(.Columns(2)) + 3 -
Application.Min(.Columns(2))
For i = 2 To NumYears + 1

sh.Cells(1, i).Value = Application.Min(.Columns(2)) + i - 2
Next i

For i = 2 To LastRow

sh.Cells(i, "A").Value = .Cells(i, "A").Value
sh.Cells(i, "B").Resize(, NumYears).Value = 0
For j = 3 To 5

sh.Cells(i, Application.Match(.Cells(i, "B").Value,
sh.Rows(1), 0) + j - 3).Value = .Cells(i, j).Value
Next j
Next i
End With

End Sub