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.

 

[celkon]

Can someone help getting this code to work for multiple worksheets?

I also need this code to be combined!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngDest As Range
Dim rngDest2 As Range2
Dim rngDest3 As Range3
Set rngTrigger = Sheet1.Range("rngTrigger")
Set rngDest = Accepted.Range("rngDest")
Set rngDest2 = Declined.Range2("rngDest")
Set rngDest3 = Inactive.Range3("rngDest")
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entered is a number or is recognizable as a number
If UCase(Target) = "ACCEPTED" 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
' Only trigger if the value entered is a number or is recognizable as a number
If UCase(Target) = "DECLINED" 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
rngDest2.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
' Only trigger if the value entered is a number or is recognizable as a number
If UCase(Target) = "INACTIVE" 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
rngDest3.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
End If
End Sub


ADDITIONAL CODE:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim G As Range, H As Range, Inte As Range, r As Range
Set G = Range("G:G")
Set Inte = Intersect(G, Target)
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Inte
If r.Offset(0, 1).Value = "" Then
r.Offset(0, 1).Value = Date
End If
Next r
Application.EnableEvents = True

 

[My Aswer Is This]

I think you should start a new posting and tell us what your wanting to do in words.

 

[WallaceBrigsby]

I used your code that you originally wrote to move rows based on a date input. I am very new to VBA and don't really know how to debug but I get the following error - run-time error '1004', Method 'Range' of object '_Worksheet' failed. Keep in mind I'm using different codenames because the workbook I'm working with is pretty expansive, instead of Sheet 1 and Sheet 2 I am using "Sheet 2" and "Sheet 6". I've made changes to your code accordingly. I've highlighted in red where the error is occurring below:

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

 

[BigC]

Hi Wallace, and welcome to the Forum

I think the message you've quoted indicates that there is a problem with a Method (action) on a Range object (e.g. defined name or range reference). Given this, and the line of code at which the error arises, I'd say there is some kind of problem with Range("rngTrigger") on Sheet1 (e.g. it doesn't exist, is incorrectly defined, you've not referenced it correctly in the code, etc.)

Correctly setting up the Defined Names "rngTrigger" (on the source sheet/s) and "rngDest" (on each destination sheet/s) is absolutely critical when using this code. You especially need to make sure you define these names (in the Excel spreadsheet screen) with the correct "scope" (i.e. Workbook or Sheet) when creating them in Name Manager. "rngDest", and I think any Name where you use CodeName.Range("Name") syntax, must be sheet-scope as qualifying the VBA reference with the codename prefix directs Excel/VBA to look for a sheet-scoped name.

Also note that the codename of a sheet is NOT what you see on the tabs at the bottom of the screen when looking at the spreadsheet, but are the sheet OBJECT name which is the first part - before "(....)" - in the list of Microsoft Excel Objects for the VBAProject you see in the Visual Basic Editor. You need to make sure you use the correct codename reference (exactly as shown in the Object list, without spaces, etc.) and in the right spot (especially if you're moving rows to more than one potential destination sheet, or moving rows from more than one source sheet)

As it can get very confusing when using more than a single source - single target application, check everything about your Defined Names, including where/how they're referenced in the code.

 

[Wasyl]

The error you're now getting indicates that you've used Excel Tables (rather than simple "range") to hold your data. Unfortunately, the code was written before I knew anything about Tables (& still know little about the VBA particular to Tables, especially regarding inserting rows). rngDest.Insert Shift:=xlDown (to Insert Copied Cells) does not work on a Table.

I'll do some research and hopefully come back with the code to make this application work with tables.

Hello BigC,
You do wonderful job, you know it. Thank you.
I wonder if you had a chance to do research concerning Tables?
Is it the only problem of this code to substitute rngDest.Insert Shift:=xlDown command with the one working on Table?
Thank you in advance.

--
Wasyl

 

[BigC]

Hi Wasyl, and welcome to the Forum

Unfortunately, no. Life got in the way and that task slipped through the cracks of my To Do List.

Perhaps if you post a description of your project with the code you have added to your table-based application (with code modified as far as possible to suit), other Forum users with expertise in VBA for Excel Tables may be able to provide the changes required, or guidance as to what to do. Given the length of this thread and the number of variations to the original application it is now a little difficult to follow, so I think it would be best to start a new thread with a link to this one.

Meanwhile, I'll add that research task back on to my To Do List as I'm sure it's something I'll need at some point in the future.

 

[Wasyl]

Pity
Actually I am interested in any working solution, no matter how inelegant
I have impression there is only small step left to go, isn't it?
I am thinking about mirror sheet with all data from source except table structure. However this is also beyond my capabilities
I posted new thread and waiting what happens:
https://www.mrexcel.com/forum/excel-questions/1044203-copy-row-after-change-another-sheet-vba.html
Thank you.

--
Wasyl

 

[bobsargent2002]

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

This is awesome, thank you! My only issue is that I have my sheets set up as tables and it keeps erroring out, because of the table. Is there a fix for this?

Thanks in advance!
Bob

 

[BigC]

Hi Bob, and welcome to the Forum.

I haven't attempted to rewrite that piece of code to suit an application using Excel Tables instead of "standard" sheet matrices (as per my post #166 , life gets in the way!) Nor have I heard/seen the results of anyone else who has tackled this problem.

I don't have a lot of spare time at the moment for this kind of project (& seldom look at this Forum these days) but will try to find the workbook holding the code you quoted (it's from the very early days of this thread) and see what I can do (after all, it is my code and quite a few people have used variations of it since the solution was first developed. ) - but I can't promise I'll get it done as it will require me to do some research/learning and be a substantial rewrite of the code as Tables are a different beast altogether with their own set of VBA objects, properties & methods in line with the different way they operate.

Cheers

 

[bobsargent2002]

Hi Big C!
Thanks for your reply! I was able to get it to work by getting rid of the table. Thanks so much for providing this code in the first place and for all of your hard work. Honestly, this feels like a big magic trick to me to be able to make the rows move over automatically by adding the date trigger. I can't tell you that many people on my team are impressed by it. I really appreciate it!
Bob