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.

 

[SteveO700]

BigC,

I have used your code and it works great, one thing that I am running into is that when the line cuts and copies into the Worksheet 2, my conditional formatting in Worksheet 2 no longer applies, even though I have the same conditional formatting in Worksheet 1 that I have in Worksheet 2. I notice that the row copies the conditional formatting, but it seems the rules no longer apply for some reason. Let me know what you think.

Steve

 

[BigC]

Hi Steve, and welcome to the Forum.

Sorry I've not got back to this since I received the notification - have got a little too much happening in my life recently.

Are you certain that the CF is exactly the same?
You should be able to review the CF rule in Wks2 after you've processed a move and evaluate/debug it to see why it is not working as expected. Check that absolute references are not linking the CF in Wks2 back to cells in Wks1 (which may now not the condition if previous values have been moved/deleted).

If the problem persists, decide whether or not you really need CF on Wks2. If you do, consider adding code that will reinstate/apply CF to the destination cells. Alternatively, rather than use CF to alert the user, consider using a formula in a separate column that returns a meaningful value (e.g. "Ok" or Error", TRUE or FALSE, etc.) you can then look at that column (or use other formula to count values to identify if there are any errors or whatever in that column.)

HTH
BigC

 

[EagleX]

I am attempting to have line items from Sheet 1 (named "Opened") moved to Sheet 2 (named "Closed") after I type "DONE" in column 1 of "Opened" sheet for each line item. I tried following the threads above without success. Can this be done from a simple Excel Workbook? I have more than 2 sheet in the workbook, but only need to have items from "Opened" sent to "Closed" on command of typing "DONE". Any help is appreciated!

 

[BigC]

Hi EagleX and welcome to the Fourm

What do you mean by "without success"? What is happening/not happening when you enter "Done" in column A? For anyone to be able to assist you on this forum you need to be more specific in order to give those who may be able to help something to work with. It would also help if you posted the code you've attached to the VBA Sheet object of your "Opened" sheet as this is most likely, but not necessarily, where the problem lies.

 

[SteveO700]

EagleX - If you go to the very first page of this forum, you will see BigC's initial post showing how to cut one row from a tab and paste into the next tab.

 

[SteveO700]

BigC,

Not a problem, and completely understand about being busy!

I have the CF in my first tab exactly the same as the CF in my second tab. You commented that I might be able to put in a code to reinstate/apply CF to the row that is just copied? The strange thing is, the CF from my first tab actually does copy over, but then when I view the CF for the second tab, there is now extra CF because of the added/pasted row that copied over.

My code as follows in the first tab:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 27 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
Dim rngDest As Range
Set rngDest = Sheet1.Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Sheet6.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entred is a date or is recognizable as a valid date
If UCase(Target) = "X" 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

My Code for the second tab:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 27 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = UCase(Target.Formula)
ErrHandler:
Application.EnableEvents = True
Dim rngDest As Range
End Sub


I basically have to go into the CF on the second tab, delete everything corresponding to the row that was just copied in, and then add into the existing CF the row that was just copied in.

Let me know what you think!

Thank you,

Steve

 

[bradellin]

Good morning/afternoon/evening,
First I would like to thank BigC for all the help I have seen given, and to all the others that have contributed.
I am a newbie when it comes to Excel and macros and VBA. I have recently had a major career change and now I find myself using Excel all the time. I want to get better at it. In regards to this thread, I want to do the same thing as the others, move one row from one worksheet to another worksheet based on a change in the first worksheet. In this case, I am using the word "Complete" to trigger the change. I have followed all the steps from the OP up to now and still cannot get it to work.

Here is the code I am using:

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

The rows/columns affected have had the names changed to reflect rngTrigger and rngDest. The sheet# are the sheets in my workbook. When I run I get asked for a macro to run. If I define a macro first then paste the code I get an error "runtime error 404" Object required, which then refers me to line 5 of the code. Additionally when I try to create a macro it creates a new module and does not let me put the code on sheet 5 (the active sheet).

What am I doing wrong?

 

[bradellin]

Sorry, that was a 424 error, not a 404.

 

[BigC]

Hi Bradellin and welcome to the Forum

Consider/check these matters:

1. Ensure that the reference to sheets used in your code (e.g. Sheet5 and Sheet8) is the VBA codename for the relevant sheets (the left-most name of each entry in the Project List when in the Visual Basic Editor, rather than the tab name which is the name inside the () and which is what you see when viewing the spreadsheet.

2. Ensure that you have created all the Named Ranges (Defined Names) in the correct location and with Worksheet scope (vs Workbook scope)

3. Replace

If UCase(Target) = "Complete" Then

with

If UCase(Target) = "COMPLETE" Then

The UCase method converts the value in Target (from your spreadsheet) to UPPERCASE, so the comparison value must also be uppercase otherwise the test will return FALSE.​

Post back with the result after any changes, being specific about where the code falls over (go to the VBE and identify the line highlighted at which the code stopped) - and include that codeline in your post so viewers are clear as to which rather than having to count lines.

HTH

 

[bradellin]

BigC, thank you for taking the time out to help everyone with this.

I have checked and double checked, gone back and reread the thread several times, and yet I still get the same error 424 Object required.

Here is the code I have attached to Sheet5: Text in RED is the highlighted text when I select debug and close the error window. I have verified that the Dest and Target Sheet names are correct, defined the ranges per your earlier instructions, and pulled out a few handfuls of hair.

Sub Worksheet_Change()
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

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. Additionally, how to I verify that my range definitions are for the worksheet, not the workbook, as you stated above?

Again, many thanks,

Brad