Move row to another sheet when option is changed

Marsh231988

New Member
Joined
Aug 9, 2019
Messages
1
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!!!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,254
Office Version
  1. 2013
Platform
  1. Windows
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)


Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,882
Messages
5,598,639
Members
414,251
Latest member
oExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top