The formula below works okay, but is a mixture of code which might cause some
problems for me down the road (and is probably not doing what I need it to do
very efficiently); I also ultimately need to setup something to copy the data
to the 'merge' worksheets which will be used by Word.

Yes, there is a reason the worksheets must be setup this way... if it helps,
the workbook (with fictional data) is on my Microsoft Office Live website:  
http://suzleigh.com/MERGE.aspx  (any comments/advice on how I can improve
anything in here would be GREATLY appreciated)

The formula below is linked to a command button

1.  Copy cells containing data from PERSONNEL (columns A through I) to IHSF
DATA ENTRY (columns B through J)
2.  Reduce the data in column I (SSN) to last 4
3.  Return the focus to B2

Sub GetIHSFData()
   
   Sheets("PERSONNEL DATA").Select
   Range("A2:J500").Select
   Selection.Copy
   Sheets("IHSF DATA ENTRY").Select
   Range("B2").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
       :=False, Transpose:=False
       
LastRow = Cells(Rows.Count, "I").End(xlUp).Row
For RowCount = 2 To LastRow
  Data = Trim(Range("I" & RowCount))
  If Len(Data) >= 4 Then
     Data = Trim(Right(Data, 4))
  End If
  If IsNumeric(Data) Then
     Number = Val(Data)
  End If
  Range("I" & RowCount) = Number
Next RowCount

  Range("B2").Select
End Sub

Thanks VERY much (also many thanks to everyone who got me to this point)

Suzanne
suz__leigh@hotmail.com