Cut an entire row and paste in another sheet

[JonReyno]

Hi guys,

I'm hoping someone can help me. I think my request will be pretty simple so here goes:

I have a 2 tabs in a workbook 'Sent' and 'Returned'. In the 'Sent' tab are a list of documents which have been sent out. When they have been returned, I would like the row to be cut and pasted into the 'Returned' tab.

I have a column in the 'Sent' tab called 'Returned' which is in column I. I would like to have this is as the date which the document had been returned and when it's been populated, be the trigger to cut and paste the row.

When a row has been cut from the 'Sent' tab, I would like that row to be removed so it will all be in a continual list.

Any ideas what code I need to be able to do this?

 

[VoG]

Try this: right click the Sent tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target.Value <> "" Then
    Application.EnableEvents = False
    With Target.EntireRow
        .Copy Destination:=Sheets("Returned").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Delete
    End With
    Application.EnableEvents = True
End If
End Sub

 

[JonReyno]

Thanks for that, that seems to work a treat.

Just a thought, is there another code that I can put in the 'Returned' tab which does the same thing 'cut and paste a row back to the 'sent' tab' if the returned date is removed from column I?

I just tested the first code and it worked, but when I deleted the date and manually cut and paste the row back to the 'Sent' tab it came up with an error, so I'm not sure if adding another code will work or not, but it's worth a try.

 

[VoG]

Try this in the Returned sheet's code module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 And Target.Value = "" Then
    Application.EnableEvents = False
    With Target.EntireRow
        .Copy Destination:=Sheets("Sent").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Delete
    End With
    Application.EnableEvents = True
End If
End Sub

 

[JonReyno]

That has worked wonders!! Thanks for your help on this, saved me a lot of hassle