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

This is a discussion on Automatically move entire rows from one Worksheet into another Worksheet in the same Workbook within the Excel Questions forums, part of the Question Forums category; I have a Task List Workbook (with 2 Worksheets)
Worksheet 1 will be just for Open Task Items. Worksheet 2 ...

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

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.

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

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")

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.

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

Originally Posted by 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.

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")

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.

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

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.

Code:

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

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

Originally Posted by 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.

Code:

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

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

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:

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

Originally Posted by 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: