How do I record a macro to transpose over 100 names and addresses in column A
of a worksheet to separate columns for a mail merge set up? i.e., column B =
name, column C = street, etc. I can record the copy, paste special,
transpose action, but I need to repeat for each of the 100 names, addresses,
incrementing down 3 rows to copy the next address and then down 1 row to
transpose/paste that address in columnar mail merge format.
--
DSM

Dave

Sub ColtoRows()
Dim Rng As Range
Dim I As Long
Dim j As Long
Dim nocols As Long
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For I = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(I, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents

End Sub


Gord Dibben MS Excel MVP

On Sat, 2 Jun 2007 15:24:02 -0700, Dave wrote:

How do I record a macro to transpose over 100 names and addresses in column A
of a worksheet to separate columns for a mail merge set up? i.e., column B =
name, column C = street, etc. I can record the copy, paste special,
transpose action, but I need to repeat for each of the 100 names, addresses,
incrementing down 3 rows to copy the next address and then down 1 row to
transpose/paste that address in columnar mail merge format.