Microsoft Excel - Reformat a table using VBA

Hi,

I have a table in the first format and I want to change it to the second format for an upload. Any sugestions? Thanks.

ID	Location	1	2	3	4	5	6	7
2057	Mexico		21%		29%	31%	23%	23%
2064	Central America				27%	24%	23%	23%
ID	Location	Month	Rate
2057	Mexico	1	21%
2057	Mexico	2	21%
2057	Mexico	3	29%
2057	Mexico	4	29%
2057	Mexico	5	31%
2057	Mexico	6	23%
2057	Mexico	7	23%
2064	Central America	1
2064	Central America	2
2064	Central America	3
2064	Central America	4	27%
2064	Central America	5	24%
2064	Central America	6	23%
2064	Central America	7	23%

Hi Cherifa,

Try this
---------------------------------------------------------------
Sub transpose()

For Each ID In Range("A2:A3")
    For i = 3 To 9
    selRow = Range("K" & Rows.Count).End(xlUp).Row + 1
    Range("K" & selRow) = ID
    Cells(selRow, 12) = ID.Offset(0, 1).Value
    Cells(selRow, 13) = Cells(1, i).Value
    Cells(selRow, 14) = Cells(ID.Row, i).Value
    Next i
Next ID

End Sub
-------------------------

Hope this help.

Pichart Y.

Thanks as always. Your solution worked fine and I am really amazed with your logic.  My table is going to be big and I have many lines and many columns. Is there a way you can generalize the code to work for any table size. For example, these two codes : Range ( "A2:A3") and i = 3 to 9 are limited to the example I gave you.   

Thanks again Pichart :)

Try this..

-------------------------------------------------
Sub transpose()
srcRow = Sheets("Source").Range("A" & Rows.Count).End(xlUp).Row
srcCol = Sheets("Source").Cells(2, Columns.Count).End(xlToLeft).Column


For Each ID In Sheets("Source").Range("A3:A" & srcRow)
    For i = 3 To srcCol
    selRow = Sheets("Result").Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & selRow) = ID
    Cells(selRow, 2) = ID.Offset(0, 1).Value
    Cells(selRow, 3) = Sheets("Source").Cells(2, i).Value
    Cells(selRow, 4) = Sheets("Source").Cells(ID.Row, i).Value
    Next i
Next ID


End Sub
---------------------------------------------------------
also see this attachment  ---> transpostData.zip

pichart Y.

Thanks Pichart you are the best.