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.

 

[ExcelNovy]

Hello BigC,

Thank you for writing this macro and all the additional information that has been posted. I have a unique issue that doesn't seem to have been explored yet. I have several sheets within the same workbook that are classified as project status (Identified/With___/Back___/Under Const./Complete). I've used your macro on each of the sheets and everything works until I do the 15th or 16th shift. At first I thought it was an issue with the first page, but then I tried attempt 15/16 on the second page and the error posted again. (I had an issue earlier with locked cells so the sheets are fully unlocked at this time.)

Run-time error'-2147417848 (80010108)':
Method 'Insert' of object 'Range' failed

Here is a pic of the debugger running.

The line issue in the debugger for sheet 3 is "rngDest2.Insert Shift:=xlDown".
Its the same line that has an issue on sheet 2, "rngDest1.Insert Shift:=xlDown".
(Note: Sheet 1 is a summary page no macro)

I'm sure its something simple or its never going to work in the present situation.

Excel 2003 workbook open in Excel 2010 for other user compatibility.

Thanks for any input, ExcelNovy

 

[ExcelNovy]

Sorry, that photo was way too small here is the macro I'm running on Sheet 3 transferring to Sheet 4.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest2 As Range
Set rngDest2 = Sheet4.Range("rngDest2")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet3.Range("rngTrigger2")) 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
rngDest2.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
End Sub

 

[BigC]

Hi ExcelNovy

I've used your macro on each of the sheets and everything works until I do the 15th or 16th shift. At first I thought it was an issue with the first page, but then I tried attempt 15/16 on the second page and the error posted again.

I'm not quite sure what you mean by "15th or 16th shift". Do you mean 15th or 16th attempt at shifting rows, or does shift refer to a working period, as in "night shift"?

Re the Method 'Insert' of object 'Range' failed error you're experiencing:

1. If all is fine until the 15th and 16th shift, is there something unique/different about those shifts (e.g. the combination of trigger values and sheets) that the macro is not handling? What if you try these trigger events earlier?
2. Have you double/triple-checked that you have created the Defined Names on each sheet, particularly the destination sheets, to ensure that they match the code in the source sheets? (Given the number of references to each range utilised [i.e. the Defined Name in the worksheet and then the VBA variables and code lines] I know when I first created the multi-destination variation of this application, I got myself a little tangled up with code trying to send rows to the wrong sheet, or without the destination range having been defined in the worksheet!)
3. Is there something below Sheet4.Range("rngDest2") that is preventing Excel from inserting more rows?
4. Have you stepped through the code by using F8 in the VBE to watch every step and see what values are being returned to the variables in the code lines, and what actions VBA is implementing when you attempt shift 15 or 16?

If you can't readily solve the issue, post back and I'll send you a Private Message with my email address to which you can send the file for me to look at (saves lots of posts back and forth via this forum trying to nut out the problem)


Cheers

 

[ExcelNovy]

Well its fixed for now. I quadruple checked my trigger and destination labels, even deleting rows and renaming them a few times. Between my VBA review, test runs and shutting down via Windows Task Manager, I caused a error with the sheet that had the file open in protected mode which warned me if I edited this file I would damage my system. It freaked me out a bit but I had come too far, why turn back now. I noticed the last row I was working on was blank. I checked my paper backup of the data and noticed there wasn't any missing data. I unprotected the file, deleted the row and saved the file under a new name. I did a few transfers from sheet to sheet and voila, no issues.

Not sure what I did but thank you for the information. If my system does finally crash I'll be sure to repost and warn anyone from doing the same thing.

ExcelNovy

 

[BigC]

I'm glad that you've managed to get it working - though it is always a worry when you don't know what caused the problem.

This is a bit of a guess (and may end-up being a wild-goose chase!), but is it possible that the "rngDest" range/s was wiped out by a successful move? That is, everything is Ok when you checked before testing, but then during an action to move a row to the specified sheet, rngDest gets overwritten.) You may want to do some single-move tests, checking after each whether all the required components are still in place. If this is happening, you need to look at what is causing this, as my original code and instructions (especially the defining of the "rngDest" range) tried to ensure that this didn't happen.

 

[bhamm]

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

Hello,

I have the following pasted into the Code of Sheet1, "OpenItems", where I want a 'y' or 'Y' to transfer over the row into worksheet "ClosedItems". It returns an error. Anyone have suggestions?

Many thanks for your help

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = ClosedItems.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, OpenItems.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entred is a date or is recognizable as a valid date
     If UCase(Target) = "Y" 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]

I have the following pasted into the Code of Sheet1, "OpenItems", where I want a 'y' or 'Y' to transfer over the row into worksheet "ClosedItems". It returns an error. [/CODE]

What type of error? (The error description indicates the source of the problem.)

Have you followed the instructions as per previous posts in this thread, particularly in regard to setting up the Defined Names and named ranges which are critical elements? The following two lines of code refer to ranges in your workbook that you must define via Name Manager before the code will work:

Set rngDest = ClosedItems.Range("rngDest")

If Not Intersect(Target, OpenItems.Range("rngTrigger")) Is Nothing Then

The first ("rngDest") is a cell on the DESTINATION sheet ("ClosedItems").

The second ("rngTrigger") is on the SOURCE sheet ("OpenItems") and defines the range in which the trigger value ('y' or 'Y') are to be entered.

Check these matters, and the others in the instructions, then come back with specific details (i.e. What you did, what happened, and what error message appeared.

Cheers

 

[bhamm]

Have you followed the instructions as per previous posts in this thread, particularly in regard to setting up the Defined Names and named ranges which are critical elements? The following two lines of code refer to ranges in your workbook that you must define via Name Manager before the code will work:

Set rngDest = ClosedItems.Range("rngDest")

If Not Intersect(Target, OpenItems.Range("rngTrigger")) Is Nothing Then

The first ("rngDest") is a cell on the DESTINATION sheet ("ClosedItems").

The second ("rngTrigger") is on the SOURCE sheet ("OpenItems") and defines the range in which the trigger value ('y' or 'Y') are to be entered.

Yes, I can confirm that rngTrigger and rngDest have been set up, without Typos.

rngDest is an entire row (currently row 3) on ClosedItems sheet. rngTrigger is an entire column on OpenItems.

Check these matters, and the others in the instructions, then come back with specific details (i.e. What you did, what happened, and what error message appeared.

Cheers

The error occurs when I as soon as I enter y or Y in rngTrigger column - the debugger appears with an arrow and line pointing to the first line, highlighted in yellow:

 Private Sub Worksheet_Change(ByVal Target As Range)

Otherwise nothing happens, nothing is transferred over, no other error message. My apologies for the vague description, I just didn't know how to describe it - it just points to an error in the first line of the code.

Very much appreciate your help!

 

[BigC]

By first line, do you mean "Private Sub Worksheet.........", or the first line of code within the Sub?
It is odd that you don't get an error message displayed.

Have you applied the correct scope (i.e. Workbook or sheet) when defining each range?

I had trouble using complete rows and columns for the defined ranges when developing this application (and also with Excel structured tables), hence why I defined the names using formulae so that extent of each range is anchored to rows that are part of the "permanent" structure of the sheet, and expand or contract dynamically to match the number of value rows that exist.

 

[bhamm]

By first line, do you mean "Private Sub Worksheet.........", or the first line of code within the Sub?
It is odd that you don't get an error message displayed.

I think I had a typo with my Worksheets. All is fixed, so now I have a Run Time Error 424, Object required. The debugger points to the line:

 Set rngDest = ClosedItems.Range("rngDest")

. To be sure, I've tried defining rngDest as both an entire column, and just as the range of cells. Neither works.

For reference, this is the full code I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = ClosedItems.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, OpenItems.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entred is a date or is recognizable as a valid date
     If UCase(Target) = "Y" 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

Greatly appreciate your help BigC!