Move row to another sheet when option is changed

[Marsh231988]

Hi all,

im a complete novice at excel, and literally have no clue what im doing but have a work issue im trying to resolve.

I have a spreadsheet (Sheet 1) listing current relationships held with other institutions; Current Columns range from A to K (but could be expanded in future). Column H lists the status of the relationship (ENABLED or REVOKED), ENABLED will be the default option, but this will change once we revoke a relationship (hence REVOKE), with the status being chosen from a drop down list.

I would like to have a macro that will move the entire row and paste into (Sheet 2) when the status is changed from ENABLED to REVOKED, and clear the row of cells from Sheet 1.

Hope someone can help me with this as its driving me mad!
Thanks!!!

 

[My Aswer Is This]

Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter the value "REVOKED" into column H the row will be copied to Sheet(2)
And the row will be deleted from Sheet(1)

Put this script in Sheet(1)

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/9/2019  12:49:53 AM  EDT
If Target.Column = 8 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, 8).End(xlUp).Row + 1
Dim r As Long
r = Target.Row
If Target.Value = "REVOKED" Then
Rows(r).Copy Sheets(2).Rows(Lastrow)
Rows(r).Delete
End If