I am using this simple user defined function that I have created in a previous post. I am using this udf in cell A1 on sheet2.
Formula in A1 on sheet2:
=ConcComma(Sheet1!B2:B6) + Enter
Function ConcComma(Substrings As Range)
Dim CELL As Range
For Each CELL In Substrings.Cells
ConcComma = ConcComma & CELL.Value & ","
ConcComma = Left(ConcComma, Len(ConcComma) - 1)