Here is code that I use to create the csv and to then split the csv. It is a
little different from exactly what you want but it is close. It takes a row
of data and makes a csv line on a new sheet. Highlinght the row you want and
run the code.

Private Const m_cMaxConcatenateRows As Integer = 1000

Private Sub MakeCSV()
Dim rngCurrent As Range
Dim rngToPaste As Range
Dim intCounter As Integer
Dim wksPasteTo As Worksheet

intCounter = 0

Set rngCurrent = ActiveCell
Set wksPasteTo = Worksheets.Add
Set rngToPaste = wksPasteTo.Range("A1")

rngToPaste.NumberFormat = "@"
rngToPaste.Value = rngCurrent.Value
Set rngCurrent = rngCurrent.Offset(1, 0)

Do While rngCurrent.Value < ""
intCounter = intCounter + 1
If intCounter m_cMaxConcatenateRows Then
intCounter = 0
Set rngToPaste = rngToPaste.Offset(1, 0)
rngToPaste.Value = rngCurrent.Value
Set rngCurrent = rngCurrent.Offset(1, 0)
Else
rngToPaste = rngToPaste & ", " & rngCurrent
Set rngCurrent = rngCurrent.Offset(1, 0)
End If
Loop
End Sub

Private Sub SplitCSV()
Dim aryCSVValues As Variant
Dim rngPasteTo As Range
Dim intCounter As Integer
Dim wksPasteTo As Worksheet

aryCSVValues = Split(ActiveCell.Value, ",")
Set wksPasteTo = Worksheets.Add
Set rngPasteTo = wksPasteTo.Range("A1")

For intCounter = LBound(aryCSVValues) To UBound(aryCSVValues)
aryCSVValues(intCounter) = Trim(aryCSVValues(intCounter))
If Left(aryCSVValues(intCounter), 1) = "'" Then _
aryCSVValues(intCounter) = Right(aryCSVValues(intCounter),
Len(aryCSVValues(intCounter)) - 1)
If Right(aryCSVValues(intCounter), 1) = "'" Then _
aryCSVValues(intCounter) = Left(aryCSVValues(intCounter),
Len(aryCSVValues(intCounter)) - 1)
rngPasteTo.NumberFormat = "@"
rngPasteTo.Value = aryCSVValues(intCounter)
Set rngPasteTo = rngPasteTo.Offset(1, 0)
Next intCounter
End Sub

HTH

"J" wrote:

Sorry, that's columns of data

"J" wrote:

I have several worksheets where I need to take rows of data like:
Row1 Row2
ABCDEF 12
GHIJKL 34
MNOPQR 56
And I want to transpose that into 2 comma-delimited rows like:
Row1 ABCDEF,GHIJKL,MNOPQR
Row2 12,34,56
The paste special function separates the data into columns. I probably need
to write a macro but I don't have much experience doing that. I appreciate
the help in getting this done. Thanks.