Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook

[joeyjay]

I have a Task List Workbook (with 2 Worksheets)

Worksheet 1 will be just for Open Task Items. Worksheet 2 will be just for Closed Items.

Worksheet 1 will consist of rows of Open Items.

The last column for each row on Worksheet 1 will either be a checkbox (for task completed) or a cell that we type a "completed date" into.

Once the last column cell is checked as completed or the cell is populated with a complete date, is there a way to have that be the trigger for the entire row to transfer over (be cut) from Worksheet to Worksheet 2 of the same Workbook?

Again, Worksheet 1 will be just for Open Task Items and Worksheet 2 will be just for Closed Items.

Thank you for your help.

 

[BigC]

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.

1. 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")
2. 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).
3. 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")

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

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.

Let me know how it goes.

 

[joeyjay]

Is there a way for me to send you my Excel file so that you can set it up for me? Would you be willing to do that? Please advise.

I don't feel confidant that I can follow the instructions correctly. And I am under a time crunch at this point.

Joey Jay

 

[cgdaniels]

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.

1. 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")
2. 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).
3. 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")

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

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.

Let me know how it goes.

BigC,

I had a inquiry very similar to joeyjay's, and the information and code you provided above were great and did exactly what I wanted. The only thing I wanted to point out for others who try to use this is that I believe in the 3rd line of your code it needs to reference "Worksheet 2" and not "Worksheet 1." I received an error message originally but once I corrected that all worked as intended.

Many thanks for the post and information!

 

[BigC]

Yeah, well spotted. I think JoeyJay encountered that problem too with the code I posted, but then we were dealing offline from this forum. Below is the final code, which also uses the codenames for the sheets rather than the tab names which could be changed by the user and thereby cause the code to fail.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet2.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet1.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

Cheers

 

[cgdaniels]

Yeah, well spotted. I think JoeyJay encountered that problem too with the code I posted, but then we were dealing offline from this forum. Below is the final code, which also uses the codenames for the sheets rather than the tab names which could be changed by the user and thereby cause the code to fail.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet2.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet1.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

Cheers

Even better, this will be great to have/know too, thanks!

 

[zoraxe]

Can this code be modified, so instead of date some word can be used? For example if "good" is written, it triggers the event... ? Maybe it is simple task, but I'm still new and experimenting in VBA.

 

[zoraxe]

I supposes this is it, in Line no.7 :
If Target = "good" Then

 

[BigC]

Yep - but to ensure the code works irrespective of which case is used (i.e. either "GOOD", "good" or "Good" - or any mix) it pays to convert the string entered to one case and then test that result, viz:

If Ucase(Target) = "GOOD" Then

 

[zoraxe]

Yep - but to ensure the code works irrespective of which case is used (i.e. either "GOOD", "good" or "Good" - or any mix) it pays to convert the string entered to one case and then test that result, viz:

If Ucase(Target) = "GOOD" Then

Great! Thank you BigC