Help with automation

[jgrappy]

I'm looking for a way to automate some data manipulating. I have rows such as the below:
Start Date------Days Supply------End Date------Unit
1/1/2011--------30-----------------1/31/2011-----540

I need to be able to chart this data by day (via PivotChart). Whereas, I need a column that would contain all days from 1/1/2011 through 1/31/2011 and another row that would have the unit (540) corresponding to each date. There are hundreds of rows like the intial example, so the point is to chart how many units there are for each day (having the pivottable summarize all the matching days) to see if it breaks a certain threshold. It would take quite a bit of time to manually transpose this data to vertical rows. Any thoughts on a good technique to do this using VBA? Thanks much for any help, hopefully I have adequately explained this!

Using Excel 2007

-Josh

 

[jgrappy]

To clarify I need to get

Start Date------Days Supply------End Date------Unit
1/1/2011--------30-----------------1/31/2011-----540
1/15/2011-------30-----------------2/15/2011-----1040

to look like this

Date-------Unit
1/1/2011---540
1/2/2011---540
1/3/2011---540
1/4/2011---540
1/5/2011---540
1/6/2011---540
etc.

And then read the next row and place it under
Date-------Unit
1/29/2011---540
1/30/2011---540
1/31/2011---540
1/15/2011---1040
1/16/2011---1040
1/17/2011---1040
etc.

 

[jgrappy]

I wrote the code, maybe not the most elegant solution, but it works.

Sub Test1()
     
    
    Range("A2").Select
    Selection.Copy
    Range("K2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("K2:K" & Range("B2").Value + 2), Type:=xlFillDefault
    
    LastRow = Range("K65536").End(xlUp).Row + 1
    Range("A3").Select
    Selection.Copy
    Range("K" & LastRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("K" & LastRow & ":K" & Range("B3").Value + LastRow), Type:=xlFillDefault
    
    
    For x = 4 To LastRow Step 1
    LastRow = Range("K65536").End(xlUp).Row + 1
    Range("A" & x).Select
    Selection.Copy
    Range("K" & LastRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("K" & LastRow & ":K" & Range("B3").Value + LastRow), Type:=xlFillDefault
    Next x
    
    Range("D2").Select
    Selection.Copy
    Range("L2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("L2:L" & Range("B2").Value + 2), Type:=xlFillDefault
    
    LastRow = Range("L65536").End(xlUp).Row + 1
    Range("D3").Select
    Selection.Copy
    Range("L" & LastRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("L" & LastRow & ":L" & Range("B3").Value + LastRow), Type:=xlFillDefault
    
    For x = 4 To LastRow Step 1
    LastRow = Range("L65536").End(xlUp).Row + 1
    Range("D" & x).Select
    Selection.Copy
    Range("L" & LastRow).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("L" & LastRow & ":L" & Range("B3").Value + LastRow), Type:=xlFillDefault
    Next x
    
End Sub