Can such a macro be created?
 

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

Can such a macro be created?
 

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 (MVP - Excel)


"Jaleel" wrote in message
...
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

Can such a macro be created?
 

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.

"Rick Rothstein" wrote:

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 (MVP - Excel)


"Jaleel" wrote in message
...
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


.