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

lucy61176

New Member
Joined
Jun 11, 2014
Messages
36
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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date Reported
[/TD]
[TD]Lost or Found?
[/TD]
[TD]Item Description
[/TD]
[TD]Name of Person Reporting Item
[/TD]
[TD]Phone No.
[/TD]
[TD]Keep item if not claimed?
[/TD]
[TD]Claimant Name
[/TD]
[TD]Claimant Phone No.
[/TD]
[TD]Date Claimed / Sent to Capitol Police
[/TD]
[/TR]
[TR]
[TD]6/1/14[/TD]
[TD]Found[/TD]
[TD]Pair of tennis shoes[/TD]
[TD]John Doe[/TD]
[TD]555-555-5555[/TD]
[TD]Yes[/TD]
[TD]Jane Doe[/TD]
[TD]111-111-1111[/TD]
[TD]6/25/14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.

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)
 
Last edited:
Upvote 0
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).
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top