Can such a macro be created?

  • Thread starter Thread starter Jaleel
  • Start date Start date
J

Jaleel

I have an Excel workbook with 2 sheets namely EMP REG and EXIT with more than
400 rows. When employees are going exit I will enter EXIT in Column Q of
Sheet EMP REG. My idea is when I enter EXIT in Column Q that entire row must
be cut from EMP REG and pasted in the last blank row of Sheet EXIT. Can this
be achieved with some code? Thanks in advance.

Jaleel
 
This macro should do what you want (although I would test it on a copy of
your workbook first, just to be sure)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastExitRow As Long, Cell As Range
For Each Cell In Target
If Cell.Column = 17 Then ' 17 = Column Q
If UCase(Cell.Value) = "EXIT" Then
With Worksheets("EXIT")
LastExitRow = .Cells(Rows.Count, "A").End(xlUp).Row
Cell.EntireRow.Copy .Cells(LastExitRow - (.Cells( _
LastExitRow, "A").Value <> ""), "A")
Cell.EntireRow.Delete
End With
End If
End If
Next
End Sub
 
Rick,

Excellent. It is working exactly as I wished! Hats off to you! So there
are people who can do wonders.

Thank you very much.
 
Back
Top