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.

 

[jthimes]

hiker95,

sorry I got busy and this got put on the back burner. Here is a link to kinda what I am wanting to do I left the info on sheet one but in the end if it gets moved to sheet2 like I've done, I would like for it to be deleted from sheet1

https://www.dropbox.com/s/fgce52ckyfj98gu/Wilson2.xls?dl=0

 

[My Aswer Is This]

You said:
I am wanting if a death date is entered for that row to copy all information for that row on sheet1 to sheet2 and delete it from sheet1

Where on the row will the death date be entered? What column number:

I need like: Column 5 or column "E"

Do not say Death column.

 

[My Aswer Is This]

Try this:
I set the Death column as column "5"
Change 5 to the correct column number
See code marked in red.
Copies row from Sheet(1) to Sheets(2) when any value is entered in column (5)

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the Sheet(1) tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
[COLOR=#ff0000]c = 5 'Change 5 to the correct column number[/COLOR]
If Target.Column = c Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
Dim ans As Long
ans = Target.Row
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, c).End(xlUp).Row + 1
Rows(Target.Row).Copy Sheets(2).Rows(Lastrow)
Rows(ans).Delete
Application.EnableEvents = True
End If
End Sub

 

[jthimes]

Thanks very much it works great!! If doing two different columns of moving how do I add the second column number. I tried c = 12 and next line down c = 18 but only column 18 will work when I do that?

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
If Target.Column = 12 Or Target.Column = 18 Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
Dim ans As Long
ans = Target.Row
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, c).End(xlUp).Row + 1
Rows(Target.Row).Copy Sheets(2).Rows(Lastrow)
Rows(ans).Delete
Application.EnableEvents = True
End If
End Sub

 

[My Aswer Is This]

I think we need to use this script and not the previous one.
This script assumes there will always be something in column "A"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
If Target.Column = 12 Or Target.Column = 18 Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
Dim ans As Long
ans = Target.Row
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(Target.Row).Copy Sheets(2).Rows(Lastrow)
Rows(ans).Delete
Application.EnableEvents = True
End If
End Sub

 

[jthimes]

That works as far as both columns moving to sheet two when a date is entered, however if more than one row has a date that is entered it deletes the previous one on sheet two. So sheet two is only keeping one record instead of all that have a date entered in that column?

 

[My Aswer Is This]

I mentioned earlier.
This script assumes there will always be something in column "A"

If you have no data in column A then the previous row will be overwritten. The scripts has to have a way of knowing what is the first empty row. So the script looks down column A when it finds the last row with data in column A it paste the row into the next row.

 

[John26]

Hi Guys,

A little help please.
I've been using the code below;

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 "Closed" is entered
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then


' Only trigger if the value entered is "Closed" or "CLOSED"
If UCase(Target) = "SEPARATED" 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

Now, What I want is to move not the entire row but a specific selected cells ("C:L", "AC:AI") only to another worksheet.
I've been tweaking and revising the code to no avail.
Needing your help for I am new to using Macros.

Thanks.

 

[celkon]

Hi all,

I am trying to have a number greater than 0 to automatically move the row into an outperformance section and then a number less than 0 move into an underperformance section on the same worksheet. I have rngDest set to the row right under outperformance and then rngDest2 set to right under underperformance but I cannot get the trigger to work.

Code below:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngTrigger As Range
Dim rngDest As Range
Dim rngDest2 As Range2
Set rngTrigger = Sheet1.Range("rngTrigger")
Set rngDest = Sheet2.Range("rngDest")
Set rngDest2 = Sheet2.Range2("rngDest")
If Not Intersect(Target, Sheet1.Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entered is a number or is recognizable as a number
If The number(Target) > 0 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
' Only trigger if the value entered is a number or is recognizable as a number
If The number(Target) < 0 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

Any help?

Hi do you mind telling me how you got this to work?