Thanks for the suggestions. I used a combination of the
suggestions, my environment is XP / xl 2003. I included
my code for reference:

Public Sub CreateAssignmentRecord()

Dim AssignmentWS As Worksheet
Dim PrevAssignmentNum As Integer

Dim StartingRow As String

Dim SubjectStreet As String

Dim Rng As Range

'1st Row is labels. Row 2 thru n are data records.
'Insert the new row below the labels.
'Copy the formulas, format and cell contents from row 3
to the newly inserted row.
'Delete the newly inserted row cell values which are not
formulas.

StartingRow = "A2"

Set AssignmentWS = Worksheets("Assignment)
PrevAssignmentNum = AssignmentWS.Range
(StartingRow).Value

Set Rng = AssignmentWS.Range(StartingRow).EntireRow
Rng.Offset(0, 0).Insert
Rng.Copy
Rng.Offset(-1, 0).PasteSpecial
Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Rng.Offset(-1, 0).PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Rng.Offset(-1, 0).SpecialCells
(xlConstants).ClearContents

Application.CutCopyMode = False

AssignmentWS.Range(StartingRow).Value =
PrevAssignmentNum + 1

'The User must assign a Subject Street Address so the
'assignment folder may be created using the street
address.
'SubjectStreet = AssignmentWS.Range("$J$3").Value

Do
'Invoke the editor on the new entry so the user
may fill it out.
Application.Run "dataform2.xla!ShowDataForm"

'Reset the selection to the inserted row in case
the user browsed
'in the editor.
AssignmentWS.Range(StartingRow).Select

SubjectStreet = AssignmentWS.Range("$J$3").Value
If SubjectStreet = "" Then
MsgBox "Error: You must assign the Subject
Street Address for a new assignment." & CR & _
" The Street Address is
used when creating the assignment folder."
End If
Loop Until SubjectStreet < ""

End If

End Sub