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.

 

[CharchBann]

Hi !
I have a similar issue (re trying to move rows from one sheet to another) but am encountering the following error :: "Error Method or Data Member Not Found"
And also the top line of my code is now highlighted in yellow with a small arrow beside it on the left hand pane.
Any guidance would be most appreciated ! Thanks in advance !!

CODE USED ::

Worksheet / Selection Change

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 "Rejected" or "Q No Response" is entered
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if status entered is "Rejected" or "Q No Response"
If UCase(Target) = "Rejected" Or "Q No Response" Then
' Ensure subequent 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.Selet
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
End Sub

 

[Michael M]

try

Set rngDest = Sheets("Sheet2").Range("rngDest")

 

[CharchBann]

try

Set rngDest = Sheets("Sheet2").Range("rngDest")

Hi,
Thank you. I made the alteration you suggested, however I am now encountering a new error: "subscript out of range"
Plus, the forth and fifth lines have now been highlighted in yellow... assuming error lies there... (Code Below)

ALSO (sorry if is silly Q) but do I not need to mention the column in which the trigger is in, in order for the whole row to move onto the second sheet ?? If so, when / where should I do this ?

Thanks again !!


This is my current code ::
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngDest As Range
Set rngTrigger = Sheets("Sheet1").Range("rngTrigger")
Set rngDest = Sheets("Sheet2").Range("rngDest")
' Limit the trap area to range of cells in which the status "Rejected" or "Q No Response" is entered
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if status entered is "Rejected" OR "Q No Response"
If UCase(Target) = "Rejected" Or "Q No Reponse" 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
' Reset.EnableEvents = True
End If
End If
End Sub

 

[Michael M]

Subscript out of range...generally means you don't have a sheet name of Sheet1 or Sheet2

 

[Michael M]

you also need to change this line

If UCase(Target) = "Rejected" Or UCase(Target) = "Q No Reponse" Then

 

[BigC]

Hi,
Thank you. I made the alteration you suggested, however I am now encountering a new error: "subscript out of range"
Plus, the forth and fifth lines have now been highlighted in yellow... assuming error lies there... (Code Below)

ALSO (sorry if is silly Q) but do I not need to mention the column in which the trigger is in, in order for the whole row to move onto the second sheet ?? If so, when / where should I do this ?

If not done already, as per the instructions in the earlier posts to this thread, you need to create two Defined Names (i.e. "rngTrigger" and "rngDest") in the Excel/spreadsheet interface, viz:

•The source range called "rngTrigger" in the active list sheet, being Sheet1. That is what 'Sheet1.Range("rngTrigger")' is referring to. (Note that "Sheet1" and "Sheet2" in this context refers to the object name (code index) of the sheet in the Microsoft Excel Objects list in the VBAProject rather than the tab name you see in the spreadsheet interface. Michael M's mod covers the situation where the tab name of Sheet2 is also "Sheet2".) You have to be careful about how you define the "rngTrigger" range, as the macro will be deleting rows from it as rows are moved. In the original application, we used a formula in the "RefersTo" field of the Defined Name 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 moved 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 fixed/known location for the macro to find when inserting the row being archived.

 

[CharchBann]

Hi,
I've tried to do what's been suggested and have re-read this thread a couple times, but I still lack a basic understanding of all that's required for the code.
So maybe it'll be better if I try to explain what I want to achieve with my spreadsheet(s).
I have a spreadsheet which uses filters (as I need to sort by company, date or status at differing times) and I want to move the entire row(s) when the status changes from either "draft" or "pending" to "Rejected" or "No Response" within the status column onto a second sheet so that I create an archive.

Also, is it possible to automatically change the status to "No Response" when the date within the deadline column lapses by a calendar month ?

This is the code (below) that I currently have, please advise (in layman's terms) how best I should amend it. Thank you !!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngDest As Range
Set rngTrigger = Sheet1("Sheet1").Range("rngTrigger")
Set rngDest = Sheet2("Sheet2").Range("rngDest")
' Limit the trap area to range of cells in which the status "Rejected" or "Q No Response" is entered
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if status entered is "Rejected" OR "Q No Response"
If UCase(Target) = "Rejected" Or UCase(Target) = "Q No Reponse" 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
' Reset.EnableEvents = True
End If
End If
End Sub

 

[BigC]

I have a spreadsheet which uses filters (as I need to sort by company, date or status at differing times) and I want to move the entire row(s) when the status changes from either "draft" or "pending" to "Rejected" or "No Response" within the status column onto a second sheet so that I create an archive.

The code, amended as necessary to suit, should do what you want. That is, upon the user entering (or selecting from Data a Validation drop-down list) either "Rejected" or "No Response" within the status column (which must be within the spreadsheet range for which a Defined Name "rngtrigger" has been created) the entire row will be moved onto a second sheet.

As mentioned in my previous post, you must create the two Defined Names in Excel otherwise the two variables declared in the code (rngTrigger and rngDest) have nothing to work with. You must also ensure that the references in the code to the sheet objects match the actual sheet/tab names.

Also, is it possible to automatically change the status to "No Response" when the date within the deadline column lapses by a calendar month ?

I'm sure it can - would require separate but similar bit of code (probably within a different Worksheet event) to continually review the deadline column for dates less than one month earlier than today which would move the relevant row if the condition is true. Get this initial one working first then we can look at this request.


Assuming you've created the two Defined Names mentioned above, the underlined bits in the following lines may be where your current problem lies:
1.

Set rngTrigger = [U]Sheet1("Sheet1")[/U].Range("rngTrigger")
Set rngDest = [U]Sheet2("Sheet2")[/U].Range("rngDest")

These are slightly different to what you had immediately after making the change Michael M suggested. If you follow Michael's suggestion (referencing tab names for the sheet objects rather than VBA code names) then the references MUST match the corresponding tab names of the sheets in your workbook. So rather than Sheet1("Sheet1") and Sheet2("Sheet2") you should have Sheets("Sheet1") and Sheets("Sheet2") respectively, viz;​

Set rngTrigger = [U]Sheets("Sheet1")[/U].Range("rngTrigger")

'Where Sheet1 is the tab name of the sheet on which Defined Name "rngTrigger" is located​

Set rngDest = [U]Sheets("Sheet2")[/U].Range("rngDest")

'Where Sheet2 is the tab name of the sheet on which Defined Name "rngDest" is located
​

2.

If UCase(Target) = "Rejected" Or UCase(Target) = "Q No [B][U]Reponse[/U][/B]" Then

Spelling! "Q No Reponse" should be "Q No Response". The words/phrase MUST match exactly what will be entered in status column of spreadsheet, and vice versa.

​

3.

' Reset.EnableEvents = True

This appears to be a combination of the explanation comment and the active code line, and in any event, has no effect as it has been commented out by prefixing the line with '.The active line should be

Application.EnableEvents = True

to turn the VBA Event Handler back on so that the next change in the worksheet will trigger the code. Without resetting the Event Handler, the macro is effectively switched off!​

Try the following corrected code (which doesn't cater for your request for an automated change of status to "No Response" when the date within the deadline column lapses by a calendar month):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[INDENT=2]Dim rngTrigger As Range
Dim rngDest As Range

Set rngTrigger = Sheets("Sheet1").Range("rngTrigger")    [I]'<< amend "Sheet1" to match actual tab name
[/I]Set rngDest = Sheets("Sheet2").Range("rngDest")[I]'<< amend "Sheet2" to match actual tab name[/I]

'Limit the trap area to range of cells in which the status "Rejected" or "Q No Response" is entered
If Not Intersect(Target, rngTrigger) Is Nothing Then
[/INDENT]
[INDENT=3]'Only trigger if status entered is "Rejected" OR "Q No Response"
If UCase(Target.value) = "Rejected" Or UCase(Target.value) = "Q No Response" Then
[/INDENT]
[INDENT=4]' 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

'Reset EnableEvents
 Application.EnableEvents = True
[/INDENT]
[INDENT=3]End If[/INDENT]
[INDENT=2]End If
[/INDENT]
End Sub

 

[CharchBann]

Okay. I did all you said. Thank you very much for your help.
However, I'm still encountering an error. Run-Time Error 424 Object Required.
How should I proceed ??

 

[BigC]

Mmmmm!

1. Which line of the code is in error?
2. As the error message indicates, an object (sheet, Defined Name) is missing, so:
   1. Check that you have you got all the "Set" statements (which set up the objects)
   2. Check the spelling of all references to sheets, names, etc. and make sure they all match. Watch for inadvertent spaces.

To test the code:

1. Insert a standard module to the project (Click Insert / Module from within the VBE)
2. Create a new standard macro, say "Sub Test()"
3. Copy the code from the Worksheet_Change event and paste into this procedure, ensuring you don't include "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" and have only 1 x "End Sub".
4. Click inside this new macro
5. Press F8 to step through each line to see if it executes correctly. You can mouse-over the variables to see what value has been assigned to them.

Post back with results of the above.
If not successful, it is probably best to send me a copy of your file - but let's cross that bridge when we get to it.