This caters for the use of dates to signal that an Open item is to be Closed (I don't know how to capture the Checkbox scenario)
I have assumed that the sheets in your workbook are actually named "Worksheet 1" and "Worksheet 2" - otherwise you'll need to change the 3rd line of code below to suit.
- Assign a Defined Name to the entire last column, or just the range of cells, on Worksheet 1 in which completed dates are to be entered as "rngTrigger" (that's "r" "n" "g" ... not "m" "g")
- On Worksheet 2, select the entire row immediately under the last entry and assign the Defined Name "rngDest" to it (newly closed rows will be inserted above this location).
- Paste the following code into the Worksheet object for Worksheet 1 (not in a Module) in the Visual Basic Editor (select the tab for this sheet, then right click and select "View Code")Note that this will not work properly if you copy a completed date and simultaneously paste it to a range of cells in Worksheet 1, so you need to tag items in Worksheet 1 one at a time.Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDest As Range Set rngDest = Worksheets("Worksheet 1").Range("rngDest") ' Limit the trap area to range of cells in which completed dates are entered as defined above If Not Intersect(Target, Range("rngTrigger")) Is Nothing Then ' Only trigger if the value entred is a date or is recognizable as a valid date If IsDate(Target) Then 'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop! Application.EnableEvents = False Target.EntireRow.Select Selection.Cut rngDest.Insert Shift:=xlDown Selection.Delete ' Reset EnableEvents Application.EnableEvents = True End If End If End Sub
Let me know how it goes.