try this
Sub Exits()
Dim rng As Range
Dim lasrow As Cell
Set rng = Sheets("Headcount as of April-2007").Range("B10:B500")
If rng Is Delete Then
If MsgBox("Delete this name?", vbYesNoCancel) < vbYes Then
Exit Sub
End If
lastrow = Sheets("EXIT - 2007").UsedRange.Rows.Count
Rows(lastrow).Select
Selection.Insert Shift:=xlDown
Cells(lastrow - 1, 1).Select
Range(ActiveCell, ActiveCell.Offset(0, lastcolumn)).Select
Selection.AutoFill Destination:=Range(ActiveCell, _
ActiveCell.Offset(1, lastcolumn)), Type:=xlFillDefault
With rng
.Cut
.Paste Destination:=Sheets("EXIT - 2007").Range(6, Rows(lastrow))
End With
End If
End Sub

"abdrums" wrote:

I have a macro that when the user delete the content of any value between the
range of B10:B500 do the following things.
1.Message box asking if they are sure to delete.
2.If yes is selected,
a. Create one last new row on sheet2.
b. Cut the name to be deleted from Sheet1.
c. Paste the name deleted on the (column F, last new row on
sheet2).
d. Exit from macro.
3. If No is selected, exit from macro.

My macro code is below but I feel that I missing something or I'm doing
something wrong:

Private Sub Exits()
Dim rng As Range
Dim lasrow As Cell
Set rng = Sheets("Headcount as of April-2007").Range("B10:B500")
If rng Is Delete Then
Answer = MsgBox(Prompt:="Delete this name?", Buttons:=vbYesNo +
vbQuestions)
If Answer = vbYes Then
lastrow = Sheets("EXIT - 2007").UsedRange.Rows.Count
Rows(lastrow).Select
Selection.Insert Shift:=xlDown
Cells(lastrow - 1, 1).Select
Range(ActiveCell, ActiveCell.Offset(0, lastcolumn)).Select
Selection.AutoFill Destination:=Range(ActiveCell,
ActiveCell.Offset(1, lastcolumn)), Type:=xlFillDefault
With rng
.Cut
.Paste Destination:=Sheets("EXIT - 2007").Range(6, Rows(lastrow))
End With
End If
End Sub

Any help will be appreciated and thanks in advance.