Move a row from one worksheet to another when a date is entered in the Date Claimed column

[lucy61176]

I have a lost and found log that has 2 worksheets: (1) Unresolved and (2) Resolved. Data is entered into the Unresolved worksheet when something is lost or found. When a date is entered under Date Claimed / Sent to Capitol Police (column I), I would like the data in that row to be removed from the Unresolved worksheet and automatically inserted in the next blank row of the Resolved worksheet. I know very little VB, so any help is greatly appreciated. Thank you

Date Reported	Lost or Found?	Item Description	Name of Person Reporting Item	Phone No.	Keep item if not claimed?	Claimant Name	Claimant Phone No.	Date Claimed / Sent to Capitol Police
6/1/14	Found	Pair of tennis shoes	John Doe	555-555-5555	Yes	Jane Doe	111-111-1111	6/25/14

<tbody>
</tbody>

 

[JLGWhiz]

This is event code for the worksheet change event. It will trigger for any change to the worksheet, however, it will not execute the data cut and paste unless the change is to a single cell in column I. To install the code, right click on the sheet name tab of the unresolved items, then click 'View Code' in the pop up menu that appears. Copy this code into the large code window pane that appears in the VB editor. Click the save icon and close the VB editor window. Once installed, anychange in the column I data will cause that row of data to be cut and pasted to the 'Resolved' Worksheet. The code uses "Resolved" as the sheet name of the destination sheet, if that is not the correct sheet name, then you need to either correct where it appears in the code, or change the sheet name to match the code. Sheet names are case sensitive in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    If Not Intersect(Target, Range("I:I")) Is Nothing Then
        If Target.Cells.Count = 1 Then
            Target.EntireRow.Cut Sheets("Resolved").Cells(Rows.Count, 1).End(xlUp)(2) 'Edit sheet name
        End If
    End If
Application.EnableEvents = True
End Sub

If you are using a version of Excel2007 or later, it is advisable to save your workbook as a macro enabled workbook (.xlsm)

 

[lucy61176]

I could not get it to work.

 

[Harry Flashman]

I could not get it to work.

I just tested it and it worked fine.

Did you put the code behind the "Unresolved" worksheet (where the code goes)? Worksheet event macros don't go in regular modules. You need to right click the worksheet tab and select view code from the menu. Post the macro into the code window that appears.

The other thing to check is the your worksheets are indeed named Unresolved (the source) and Resolved (the destination).

 

[lucy61176]

Yes, it is in the Unresolved worksheet. Nothing happens when I enter a date in column I.

 

[lucy61176]

Sorry! I closed out of my workbook and then re-opened it, and it works. THANK YOU! Now is there a way to also delete the row that was cut from the Unresolved worksheet so that I do not have blank rows in between rows filled with data?

After trying it with multiple rows in various locations, it does not seem to be working. It only cut and copied over 3 rows out of the 7 of which I added a date in column I.