Creating Macro to transpose from Column to Row

[Deaneric]

Hi All, this seams so easy but i just can't figure it out!!

I have list of names and addresses in a column, they are in groups of five e.g. cell A1 - Name, A2 - Address 1, A3 - Address 2, A4 - Address 3, A5 - Postcode.
What i need to do is copy the first group of five and then transpose, then move down to the next group of five and transpose and so on till the end of the column.

Any help would be greatly appreciated.

Thanks

Dean

[bonny24tycoon]

Hi Deaneric,

Welcome to the forum

Can you upload an example workbook with some comments outlining your desired output?

[Deaneric]

Thanks

I have attached the spreadsheet, you'll see that the addresses are all in one column. I need to do a mail merge so each one of the 5 address cells needs to be transposed. I what a macro to do that.

[bonny24tycoon]

Try this code..

Public Sub TransposeData()

Dim LastRow As Long
Dim NextRow As Long
Dim i As Long

    Application.ScreenUpdating = False
    With Worksheets("Swainson1")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To LastRow Step 5

            .Cells(i, "A").Resize(5).Copy
            NextRow = NextRow + 1
            .Cells(NextRow, "B").PasteSpecial Paste:=xlPasteAll, transpose:=True
            Next i

            .Rows(NextRow + 1).Resize(LastRow - NextRow).Delete
            .Columns(1).Delete
    End With
    Application.ScreenUpdating = True
End Sub