Copying & Pasting with recorded macros

[DanP]

I wish to create a macro where I can cut the last word from a cell and paste
into the cell next to it. Then move to the cell below the previous cell.
That way, I can repeat the macro.

I'm quite familiar with the relative macro button and have used it lots of
times for other macros that do not require copying/cutting and pasting. I
have pasted the macro below for you to look at. The macro was recorded. The
problem that I have is everytime I use the macro, it copies and pastes the
same information from the original cell to any new cell I use.
======================
Sub Macro15()
'
' Macro15 Macro
' Macro recorded 4/15/2009 by 629237
'
' Keyboard Shortcut: Ctrl+Shift+K
'
ActiveCell.Select
ActiveCell.FormulaR1C1 = "James A. "
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, -1).Range("A1").Select
End Sub
======================
The original words in the cell are James A. Solano. But the words in each
cell below are different.

If someone could help, I would really appreciate it. By the way, I'm using
Excel 2002.

Thank you

 

[Sheeloo]

Try this
Sub Macro15()
x = Split(ActiveCell)
ActiveCell.Offset(0, 1) = x(UBound(x))
ActiveCell.Offset(1, 0).Select
End Sub

Following line in your macro is assinging James A. as the value of the
current cell...
ActiveCell.FormulaR1C1 = "James A. "
which is then copied to the next cell.

 

[DanP]

Sheeloo,

Thank you. It's almost what I need. It copied the last name "Solano" from
the first cell and pasted it into the next cell, but Solano was not removed
from the first cell. The first cell still shows "James A. Solano"

 

[Sheeloo]

Try
Sub Macro15()
x = Split(ActiveCell)
ActiveCell = x(0)
ActiveCell.Offset(0, 1) = x(UBound(x))
ActiveCell.Offset(1, 0).Select
End Sub

It will remove the middle word if the cell has more than two..

 

[DanP]

Sheeloo,

Sorry, that doesn't do anything.

 

[DanP]

Sheeloo,

I tried it again. It's working. It cuts off the middle initial but I can
live with that.

Thank you much.

 

[Sheeloo]

Where do you want the middle initial to go? Original cell or new cell?

Also do you really want to execute it for each cell?

 

[DanP]

Sheeloo,

The middle initial would go in the original cell. The problem that I have
is there are cells that don't have a middle initial. Right now, I have about
3,500 cells (names).

 

[Sheeloo]

In that case, select your range (single column) and run the macro below
Sub splitCell()
For Each c In Selection
tmp = ""
x = split(c)
If UBound(x) > 0 Then
For i = 0 To (UBound(x) - 1)
tmp = tmp & x(i) & " "
Next i
tmp = Left(tmp, Len(tmp) - 1)
c.Offset(0, 1) = x(UBound(x))
c.Offset(0, 0) = tmp
End If
Next
End Sub

 

[DanP]

Sheeloo,

Hurray!!!!! It's just what I needed. I really, really appreciate it.