Separating first and last names - vba

[lordriel]

I've seen several methods for separating names, or text, from one cell to two or more. However, none of them give a vba answer.

How would I code it, were it to be done when hitting a command button?

 

[Peter_SSs]

Anyway this is what I did... The first line works and the second line does not.

 Sheets("Cpl-Child Info").Range("BP1").Value = "First Name"
  Sheets("Cpl-Child Info").Range("B2", Range("B" & Rows.Count).End(xlUp)).TextToColumns Destination:=Range("BP2"), DataType:=xlDelimited, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9))

You noted that you had qualified the range with the sheet, but you only did it for the two blue ranges. You would need to add qualifications for the two red ones as well.

 

[Rick Rothstein]

Here is another macro that should work...

Sub FirstNames()
  With Sheets("Cpl-Child Info").Range("B2", Sheets("Cpl-Child Info").Cells(Rows.Count, "B").End(xlUp))
    .Offset(-1, 66)(1).Value = "First Name"
    .Offset(, 66) = Evaluate(Replace(Replace("IF(#!@="""","""",LEFT(#!@,FIND("" "",#!@&"" "")-1))", "@", .Address), "#", "'Cpl-Child Info'"))
  End With
End Sub