Help with messy formula

[Suzanne]

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
(e-mail address removed)

 

[carlo]

Hi Suzanne

I would do it like that:
-------------------------------------------------
Sub GetIHSFData()

Sheets("PERSONNEL DATA").Range("A2:J500").Copy

With Sheets("IHSF DATA ENTRY")
.Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

For RowCount = 2 To .Cells(.Rows.Count, "I").End(xlUp).Row
Data_i = Right(Trim(.Range("I" & RowCount).Value), 4)
If IsNumeric(Data_i) Then
Number_I = Val(Data_i)
End If
.Range("I" & RowCount) = Number_I
Next RowCount

.Select
.Range("B2").Select

End With

End Sub
-----------------------------------------------------------------

I would not use Data and Number as variablenames, they may be used by
VBA.

I deleted the selecting of the cells, because it's unnecessary.

hope thats what you wanted

Carlo