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.

 

[bql2016]

bql

No worries - that's what this Forum is all about.

Please post back to advise whether this idea is successful or not.

Hi BigC
I`m happy to say that the idea was successful and I am very grateful!

 

[BigC]

Great to hear - glad you were able to work it out.
Thanks for the feedback.

 

[mjhartf]

Can someone please assist me with this scenario?

I need a Range of Cells in a Row on Sheet 1 to automatically move from that sheet to other sheets within that Workbook, based on a numerical value in a specific cell, within that Range of Cells. All sheets will be identical to Sheet 1, except for their Names. I also need all the Formulas in that Range being moved, as well as all Formatting, Conditional Formatting Rules, and Drop Down Lists to move with it.

Here are the Workbook specifics:

Workbook: Work_Orders
Sheet 1: New
Sheet 2: Open
Sheet 3: Completed
Sheet 4: Cancelled
Range of Cells: C9, L9
Cell with #: D9

Cell D9 will have these numerical values that trigger which sheets to move it to:

1 = Open
2 = Completed
3 = Open
4 = Cancelled
5 = Cancelled

I've tried manipulating the script in this Thread, but haven't been successful.

Thanks.

 

[RobertoJ]

Hi,

This thread has been a great read that has totally helped me with a spread sheet at work.

I have used the original code for a spread sheet where I have two tabs, Plans and Complete.

The code works perfectly, although I have amended it slightly as I only wanted the data to be cleared not the row deleted.

However, the code currently selects the entire row to be copied and pasted into a new tab, which is great and what I want it to do, but it then clears the whole row and I only want column C to P cleared, I want the data in columns A & B to be left as is, not cleared.

Column P is where I am entering 'complete' to run the code

Can anyone offer any assistance on how to do this.

The current code I am using is below.

Many thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Sheet6.Range("rngDest")
' Limit the trap area to range of cells in which the status of "Closed" is entered
If Not Intersect(Target, Sheet2.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entered is "Complete" or "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.Copy
rngDest.Insert Shift:=xlDown
Selection.ClearContents
' Reset EnableEvents
Application.EnableEvents = True
Range("A" & (ActiveCell.Row)).Select
End If
End If
End Sub

 

[BigC]

Hi RobertoJ

Glad to hear that you find the code useful and that you've been able to adapt it to suit your application - save for this piece about which you've requested assistance.

Selection.ClearContents

is the line that you need to modify\replace.

Selection

is the range object being worked on - in this case an entire row, so you need to "convert" this into the target cells in the selected row and then clear just them.

Selection.Row

will return the row number for the target row.

Columns C to P are column numbers 3 to 16, so provided this target range doesn't change (if it does you'll need to change the column numbers, or convert this very basic code to something more dynamic), you can use these numbers ("hard-coded" in your sub.) to define your range object to be cleared.

The end result is this single line of (not very elegant! ) code to substitute for Selection.ClearContents:

Range(Cells(Selection.Row,3),Cells(Selection.Row,16)).ClearContents

Hope this helps.

 

[RobertoJ]

Hi BigC,

Thanks for taking the time to look at this, and although your suggestion may not be very elegant to yourself, it has worked perfectly for me.

Thanks for your help, it is very much appreciated.

 

[KrayzieBARE]

Hello,

I am new to the forum and have been going through a lot of the information on these pages and it has been very helpful.

I was wondering if someone could possibly give me a bit of guidance on automatically moving rows from one worksheet to another although perhaps a little more specific to my situation. I have a very basic Workbook with only 3 worksheets currently in it. All I need to do is move the entire row of our enquiry log (Tender & Quote Log) to the (Completed & Quoted) sheet once a completion date has been entered into the last cell in the row (from the Tender & Quote Log). Using the information I found in this post and the limited knowledge I have put the following code into the Visual Basic screen and although it initially looked like it was going to work as it selected the entire row when I entered a date into the final column but then it flagged the 12th line "rngDest.Insert Shift:=xlDown" on the code in the Visual Basics screen. I have named the 6th row on the "Completed & Quoted" worksheet to "rngDest" in the name box at the top left of the worksheet and have labelled the last column of the "Tender & Quote Log" as done using the "Create from Selection" button under the Formulas Tab.

The code I have is as follows -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Worksheets("Completed & Quoted").Range("rngDest")

' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Range("Done")) 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

Thank you very much for any guidance you can provide. I appreciate that this is probably fairly basic and that my skills aren't up to scratch but can seem to figure it out myself.

 

[PPScoordinator]

Hi,
I am new here and wondering if you can help me with my scenario. I will try to be brief, but still be clear about what I need to have happen.
I have an workbook with the following sheets: Boswell Day 2, Boswell Day 3, Boswell Day 4, Boswell Sent, Webb Day 2, Webb Day 3, Webb Day 4, Webb Sent.

I am taking over using/entering the data onto these sheets. The current process, is each morning I have to manually cut/paste everything from Boswell Day 4 into Boswell Sent. Then I go into Boswell Day 3, cut/paste everything into Boswell Day 4, then do the same with everything from Boswell Day 2 into Boswell Day 3. (then do the exact thing for the Webb sheets)

Is there any way to trigger these items to automatically move over one sheet (until they reach the Sent sheet - then they stay there) ? They need to move at the beginning of each day. (oh and there will be days that there may not be any information on a certain sheet - I don't know if that would create a problem with any coding)
And to reiterate.. they need to be activated (if that is the right word) to move in reverse order (day 4 to sent first, then 3 to 4, then 2 to 3)

I am really hoping someone has some solutions for me, because all this cut/pasting just seems so laborious.

Thanks in advance for any help/advice you might have
Julie

 

[JB15265]

Hi there,

Sorry if this has been answered previously.

So I've used one of the original codes on page 1, but with ammendments suggested on page 2 so that line 7 contains "If Target = "Yes" Then" instead (see quote below).

However, I've noticed a fatal error keeps occuring when the number of rows in sheet 2 reaches a critical point, or when the code has been run a certain number of times. This problem is not specific to the data row, so a rogue row of data does not seem to be the problem.

After roughly row 16 in my new spreadsheet (13-14 uses of the code), the code reports an error, saying it is unable to insert the row in sheet 2, and highlights the "rngDest.Insert Shift:=xlDown" line. At that point, the normal excel sheet goes completely blank/crashes, and I'm unable to undo anything. I have to use task manager to terminate excel. I can't see any cause for this problem - it works fine for a certain number of rows, and then suddenly crashes.

Does anyone know what the problem is here?

Thanks!!

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 Target = "Yes" 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

 

[jthimes]

So I input this code and it is running with no errors I'm assuming because I get nothing saying there is an error. However nothing is happening when I input a date into the cell that I am using as my rngTrigger. Any help would be greatly appreciated.