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]

1.

I do have a question, which may seem obvious to others but not this noob; if I select Sheet5 view code and then past this in, when I try to run it it forces me to create a Macro name, which it then inserts this Macro in the Modules, not in the workbook.

The Worksheet_Change() procedure is an Event macro, which means it is automatically triggered by events occurring within Excel, so it is not one that the user executes manually.

2. Given the error message you're receiving (indicating that an object your code is referencing doesn't exist), and your last question at the end of your post, viz;

Additionally, how to I verify that my range definitions are for the worksheet, not the workbook, as you stated above?

I'd suggest that you haven't created the Named Ranges with the correct scope (i.e. worksheet rather than workbook).​

Workbook scope is the default way of creating Named Ranges, which makes them accessible globally from anywhere in the workbook. To create a worksheet scoped name, you must select the sheet name in the drop-down box within the "Scope" field of the "New Name" dialogue box. If you need to change the scope, you'll have to delete the workbook scoped name and recreate it with worksheet scope (there is no "re-scoping" tool available in native Excel.)

 

[bradellin]

Thank you BigC.
So, I managed to place the range definitions within the worksheet and now I get a new message.

Run-time error '1004':
Cannot complete operation: You are attempting to change a portion of the table row or column in a way that is not allowed. This may occur when attempting to re-arrange cells within a table that affect other table cells in an unexpected way.

When I elect debug, this is the line of code that is highlighted:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet8.Range("rngDest")




' Limit the trap area to range of cells in which the status of "Complete" is entered
If Not Intersect(Target, Sheet5.Range("rngTrigger")) Is Nothing Then




' Only trigger if the value entered is "Complete"
If UCase(Target) = "COMPLETE" 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

At least now I am getting a different message.

 

[BigC]

Run-time error '1004':
Cannot complete operation: You are attempting to change a portion of the table row or column in a way that is not allowed. This may occur when attempting to re-arrange cells within a table that affect other table cells in an unexpected way.

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.

 

[bradellin]

I am actually trying to fix/edit/consolidate forms and schedules created by other people. I may recreate this one from scratch using what I have learned here and seeing if I can get it to do what I want.

Thanks for all the help (and please, continue the research, you may have an answer before I can recreate the form.)

 

[NewCat123]

I am very new to codes in excel. Could someone please break this down a little better for me? I believe based on what I am reading that this code would work for my purpose, but I am not sure exactly on how to execute this in my sheet.

Thanks.

 

[BigC]

Hi NewCat123 and welcome to the Forum

It is difficult for anyone to help you if we don't know the problem you're trying to solve, we can help you a lot better if you provide an outline of what it is you are seeking.

In the meantime, read post #21 of this thread (about page 3), and a few more following, to get the gist of how this application was (initially) designed to work. If you still have issues after trying to implement it, then come back to this thread with specifics.

Cheers

 

[My Aswer Is This]

This can be easily done using Vba. But you must tell us what the criteria is for this event to happen.
For example:

If I enter any value into any cell in column (A) then copy that entire row to Sheet(2)

 

[NewCat123]

I am trying to understand more of the specifics in the coding. So as I am reading this, I know that it would not be as simple as a copy and paste for it to do what I am doing.

So what I am trying to do is once a key word is entered into a box, in my case (H3), I want it to move to another sheet with in my spreadsheet. I am not really sure what I would need to substitute within your code to make that work for me. I tried to play around with it, but no results. Would you care to please elaborate more in depth?

Thanks.

 

[NewCat123]

I am not just trying to move the one cell. I am trying to move that entire row to another sheet.

 

[My Aswer Is This]

New Cat.

I think you need to start a whole new Posting. And say exactly what your wanting to do instead of piggy backing off this posting from 2012

There have been several people here trying to help you but you need to give exact specifics. Look at your lasting posting.
You said quote

"So what I am trying to do is once a key word is entered into a box, in my case (H3), I want it to move to another sheet with in my spreadsheet."

OK. What key word????? See you gave no specifics.
Then you said " key word is entered into a box" (H3) is not a "box"
H3 is a cell


Then you said:"I want it to move to another sheet with in my spreadsheet."


Move what???

Then in post # 119 you said "Entire Row"

So all you want is if some unknown value is entered into Cell(H3) you want row Row 3 moved to some other unknown Worksheet in your current workbook

Do you see our problem here you have not given but one specific and that is "H3"

We try to help people here but we cannot help you when you will not give us specifics we should not have to keep asking for specifics.