Give this macro a try (changing the Source and Destination assignments as
needed)...

Sub SplitCellLines()
Dim Source As Range, Destination As Range, Lines As Variant

Set Source = Worksheets("Sheet4").Range("C1")
Set Destination = Worksheets("Sheet5").Range("C1")

Lines = Split(Source, vbLf)
Destination.Resize(UBound(Lines) + 1) = _
WorksheetFunction.Transpose(Split(Source, vbLf))
End Sub

--
Rick (MVP - Excel)


"Mike Miller" wrote in message
...
Hi I have not done much excel programming but have a task I need some
help with. I have a worksheet with information that I need to copy
and maniplulate onto another spreadsheet. the data in worksheet one
looks as follows:

Row Col1 Col2 Col3
A B 1
1 2
3

Basically in the source worksheet you have 3 columns and in col3 you
have some text that has new line characters. i need to copy this row
to my new worksheet and split col3 at each new line charcter so that
it looks this in the new worksheet:

Row Col1 Col2 Col3
1 A B 1
2 2
3 3

Any quick way to do this with VBA would be very helpful