Excel 2013 Merge Data

[jfcby]

Hi,

I have a worksheet that I trying to merge data from multiple cells into one cell.

Lets say that worksheet one columns A1-E1 are labeled as Column 1 through Column 5. Each cell in column 1 is labeled row1-column1, row2-column1, etc.

My macro will put the column header and row1-column1 etc in worksheet 2 row 1 column on. The problem I'm having is when the cell text is wraped the each value is on a separate line like...

row1-column1
row2-column1
row3-column1

I need the text to side by side like row1-column1, row2-column1, row3-column1.

How can I get my macro to merge the the data so that it will be displayed like row1-column1, row2-column1, row3-column1 when the cell is merged?

Macro:

'Copy sheet1 data to sheet2
is2 = 2
For is1 = 4 To s1rowNum 'rows
Worksheets(wksName2).Range("A" & is2) = Worksheets(wksName1).Range("A" & is1)
Worksheets(wksName2).Range("B" & is2) = Worksheets(wksName1).Range("B" & is1)
Worksheets(wksName2).Range("C" & is2) = Worksheets(wksName1).Range("C" & is1)

For is3 = 1 To 3 'columns
If Worksheets(wksName1).Cells(is1, is3) <> "" Then
cTrim1 = Trim(Worksheets(wksName1).Cells(3, is3).Text)
cTrim2 = Worksheets(wksName1).Cells(is1, is3).Text
'vCell = Trim(vCell & Worksheets(wksName1).Cells(3, is3).Text & "-|-" & " (" & Worksheets(wksName1).Cells(is1, is3).Text & ") ")
vCell = vCell & cTrim1 & cTrim2
End If
Next

Thanks for your help,
Frankie

 

[GS]

In a standard module...


Option Explicit

Sub XferColsToRows()
Dim vData, n&
vData = Sheets("Sheet1").UsedRange
For n = LBound(vData) To UBound(vData, 2)
With Sheets("Sheet2")
.Cells(n, 1).Value = _
Join(Application.Transpose(Application.Index(vData, 0, n)),
",")
End With 'Sheets("Sheet2")
Next 'n
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Alternatively...

Sub XferColsToRows()
Dim vData, n&
vData = Sheets("Sheet1").UsedRange
For n = LBound(vData) To UBound(vData, 2)
Sheets("Sheet2").Cells(n, 1).Value = _
Join(Application.Transpose(Application.Index(vData, 0, n)), ",")
Next 'n
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion