Can such a macro be created?

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
 
R

Rick Rothstein

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
 
J

Jaleel

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top