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]

Hi ansmith

.... I am trying to move an entire row when column P is changed to "closed". Do I have to Define a name "closed"? on both sheet1 and shee2? I can't get the macro to run and when I step through it I have a bug on the row:
If Not Intersect(Target, Sheet1.Range("Closed")) Is Nothing Then

Following the original code and that adapted for zoraxe, you need to define two Named Ranges:

1. The source range called "rngTrigger" in the active list sheet (Sheet1). That is what 'Sheet1.Range("rngTrigger")' is referring to.
2. A Name to define the point in the archive sheet (your Sheet2) where the row to be archived is to be inserted - here we called it "rngDest", and is referred to in the macro by 'Set rngDest = Sheet2.Range("rngDest")'

Note that "Sheet1" and "Sheet2" do NOT refer to the tab names of the sheets in the Excel interface but to the VBA Object name which you can only see in the Visual Basic Editor. Ensure you read all the earlier posts in this thread for a few other matters to take into account.


This is how your code should look:

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 the status of "Closed" is entered
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then

' Only trigger if the value entered is "Closed" or "CLOSED"
     If UCASE(Target) = "Closed" 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

' Do the move
        Target.EntireRow.Select
        Selection.Cut
        rngDest.Insert Shift:=xlDown
        Selection.Delete
        Application.EnableEvents = True
    End If
End If
End Sub

The way the Worksheet_Change event works is by detecting a change in a specified range (in this case any cell in the range "rngTrigger"). The variable "Target" is assigned by the event to the cell that was changed (wherever in the sheet that may be), and the code line "If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then" identifies whether this cell is within the specified range "rngTrigger" (the 'Intersect' of the two ranges.) - though it is expressed in a double negative fashion (i.e. Not [...] Nothing = Something!)
Also note that you have to be careful about how you define the "rngTrigger" range, as the macro will be deleting rows from it as rows are archived. In the original application, we used a formula in the "RefersTo" field to create a 'dynamic' range definition that grew or shrunk according to the number of active entries in it.

Hope that helps

 

[blaisdell01]

Anythign else need to be changed to achieve the above (it being a word that activates it instead of date) like maybe line 6?? Thanks!!

 

[BigC]

Welcome to the Forum.

To date, the solution offered to the OP has been adapted and modified about four times (each time for slightly different applications). Having said that, post #21 below in response to post #20 was to cater for a word trigger rather than a date. Read the full thread from the start, make the changes suggested to suit your purpose, and see if it works. If not, then come back with specific details as to what you have built and what is not working.

 

[blaisdell01]

Welcome to the Forum.

To date, the solution offered to the OP has been adapted and modified about four times (each time for slightly different applications). Having said that, post #21 below in response to post #20 was to cater for a word trigger rather than a date. Read the full thread from the start, make the changes suggested to suit your purpose, and see if it works. If not, then come back with specific details as to what you have built and what is not working.

So I have put the below into the VB Editor and closed it. Then selected "Closed-Lost" on one of my rows and it is not moving? Am I missing a step? Tx!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet2.Range("rngDest")
' Limit the trap area to range of cells in which the status of "Closed-Lost" is entered
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entered is "Closed-Lost" or "CLOSED-LOST"
If UCase(Target) = "Closed-Lost" 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
' Do the move
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
Application.EnableEvents = True
End If
End If
End Sub

 

[BigC]

As per the various posts in this thread (particularly #21 below), you need to do more than just put the code into the VB Editor!

Have you:

1. Put the code in a standard module or in the VBA Object for the "live"sheet? (It needs to be in the VBA Object under the "Microsoft Excel Objects" tree)
2. Made sure that the VBA Object names of your "live" sheet and "archive" sheet are "Sheet1" and "Sheet2" respectively to match the references used in the code? (These names do NOT refer to the tab names of the sheets in the Excel interface but to the VBA Object names which you can only see in the Visual Basic Editor under the "Microsoft Excel Objects" tree. Either change the code or change the object names in the Properties window.)
3. Defined two Named ranges?
   • The source range called "rngTrigger" in the active list sheet (Sheet1). That is what 'Sheet1.Range("rngTrigger")' is referring to. You have to be careful about how you define the "rngTrigger" range, as the macro will be deleting rows from it as rows are archived. In the original application, we used a formula in the "RefersTo" field to create a 'dynamic' range definition that grew or shrunk according to the number of active entries in it.
     
   • A Name to define the point in the archive sheet (Sheet2) where the row to be archived is to be inserted - here we called it "rngDest", and is referred to in the macro by 'Set rngDest = Sheet2.Range("rngDest")'. In the original application we had a "fence" (enter \= in the cells) to designate the bottom of the archived row table, and assigned the name ("rngDest") to the cell in column A of this row. This creates a known location for the macro to find when inserting the row being archived.

Also be aware that to resolve some issues for some of the posters I liaised directly (i.e. outside the Forum) with them to get their application working, so all the info required is not necessarily included in this thread.

After you've attended to the above, come back with any problems or to let us know that you're up and running.

 

[mariesnell]

Hi All,

I found all the information on this discussion very helpful but I am getting an error that I'm sure is easy to fix but I can't figure out how - would absolutely LOVE some help!

I have two workbooks in one worksheet. One is named 'Active' and the other 'Archive.
The last column (column P) is titled 'Complete'.
When I type 'Yes' into the complete column I'd love that row to up & move to the 'Archive' sheet as a new row and be removed from the 'Active' sheet.
I've read, reread and tried all of the above but have the error 'Run-time error '1004': AutoFilter method of Range class failed'.
The code I have is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range

Const YesCol As String = "P"

Set Changed = Intersect(Target, Columns(YesCol))
If Not Changed Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With Intersect(ActiveSheet.UsedRange, Columns(YesCol)).Offset(1)
.AutoFilter Field:=1, Criteria1:="=YES"
With .Offset(1).EntireRow
.Copy Destination:=Sheets("ARCHIVE") _
.Range("A" & Rows.Count).End(xlUp).Offset(1)
.Delete
End With
.AutoFilter
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub


The debug mode shows .AutoFilter Field:=1, Criteria1:="=YES" highlighted as the issue.

What simple thing have I missed to make the above code work?

Thank you in advance for your time with my issue

 

[BigC]

Hi mariesnell & welcome to the Forum

Why are you using Autofilter in this code - it just adds a level of complication you don't need? The error is probably because Autofilters are not in place and no specific filter (e.g. column P = "Yes") has been applied.
Have another read of the code in the earlier posts (particularly #21) and you'll see that Autofilter is NOT used.

The basic idea of the Worksheet_Change event in the application covered by this thread is that something is done (e.g. move row) if the specified condition is True (e.g. cell value = "Yes") for a cell (Target) within the designated range (e.g. column P).
So in your case, rather than mess around with Autofilter, I would suggest that your code should merely test that the changed cell (Target) is (a) within column P (intersection of Target and column P is NOT Nothing) AND (b) contains "Yes".

BTW, your statement "I have two workbooks in one worksheet. One is named 'Active' and the other 'Archive." is a little back-to-front. You have two worksheets in one workbook.

HTH

 

[p9j123]

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

Hello Big C,

I am looking for the same macro and I am glad this thread was revived. I follow your macro on post#5, however am I doing something wrong here? there is no error indicator but nothing is happening when I modify the last column.

I modified the macro and made "COMPLETED" as an indicator to move the row. Below is the link of my test file, really appreciate if you can take a look at it.

https://app.box.com/s/q21yr3vx90xyvcysbwip

Also, I have the same concern as post #11. Instead of moving to the other sheet of same workbook, can we move it to another workbook? Let say Completed.xlsx Sheet 1.

Hope you can assist me with this.

 

[BigC]

Hi p9j123

Problem #1
Your spreadsheet worked fine for me. One reason for applications not working when event procedures (e.g. Worksheet_Change) are utilised is that VBA's event handler has been deactivated (e.g. by some interruption to the code). To fix it you need to MANUALLY reset the event handler. I have the following separate sub that I run when I suspect this is the problem:

Sub ResetEnableEvents()
'   Resets Application.EnableEvents status to True when an event macro is interrupted.
'   Needs to be run manually

Application.EnableEvents = True

End Sub

Problem #2
It's been some time since I looked at this thread, so I'll need to review it to see what, if anything, we resolved in regard to moving rows to another workbook - this may take a while.

 

[mariesnell]

Hi Big C,

Thanks for the welcome & the help but I'm afraid I'm more lost than before. I can't even get through your basic below information from Post #21 as I don't understand how to even define these ranges. 1.5hrs on and I am still scratching my head.

Following the original code and that adapted for zoraxe, you need to define two Named Ranges:

1. The source range called "rngTrigger" in the active list sheet (Sheet1). That is what 'Sheet1.Range("rngTrigger")' is referring to.
2. A Name to define the point in the archive sheet (your Sheet2) where the row to be archived is to be inserted - here we called it "rngDest", and is referred to in the macro by 'Set rngDest = Sheet2.Range("rngDest")'

I'm very out of my depth obviously.

I did see that the previous code I was using had 'Auto Filter' in it but I didn't know how to remove it correctly. Clearly I had a dyslexia moment in my workbook & worksheet explanation and I am on the wrong thread as this isn't the one I was reading previously & joined MrExcel to query so my level of lostness is now on a whole new plane as I can't even figure out why areas of a worksheet need to be defined when they are already named or referenced with Excel anyway (ie Column P which has the heading Complete). I just wish I could manipulate the code to perform what I am sure is a basic action.

I did find this simple looking code, is there a way I can edit it to make it work for me as my attempts did not work (which is to be expected given my very basic understanding) as it looks very straightforward:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If UCase(Target.Value) = "COMPLETE" Then
Target.EntireRow.Copy Destination:=Sheets("Archive"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub

(https://au.answers.yahoo.com/question/index?qid=20130415094757AAUUr4F)

Sorry for my lack of understanding and excel macro nativity. It really is another language to me and I don't spend much time on it to form any fundamental skills.