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