transpose macro

**oeldere** · 08-05-2012, 08:52 PM

I want to transpose data into a table, so i can use pivot table.

I want a macro to do that for me.

I attached an example file.

In the file you see how the data is built up, and how i want to get the data.

If it is easier to change the columns, that's no problem.

If it is easier to put the result on another worksheet, that's no problem.

Do you have question on this item, just ask.

**rylo** · 08-05-2012, 09:11 PM

Hi

Here goes

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Blad2")
  OutSH.Range("A1:C1").Value = Array("Name", "Date", "Amount")
  
  For coll = 2 To Cells(3, Columns.Count).End(xlToLeft).Column
    For roww = 4 To Cells(Rows.Count, 1).End(xlUp).Row
      outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      OutSH.Cells(outrow, 1) = Cells(roww, 1)
      OutSH.Cells(outrow, 2) = Cells(3, coll)
      OutSH.Cells(outrow, 3) = Cells(roww, coll)
    Next roww
  Next coll
  With OutSH
    .Range("A:C").Sort key1:=.Range("B1"), key2:=.Range("A1"), Header:=xlYes
    .Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row).NumberFormat = "mm-dd-yy"
  End With
    
  
End Sub

rylo