Automatic Move

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet that has a column titled 'status'. Within this column I
have several status' like deferred, completed, in progress, etc. I want to
create code or something like it that will automatically move the row to
another sheet in the workbook when I change the status to complete. I do not
want the row to remain on the original sheet. I only want the information to
appear on the complete sheet.

How can I do this?

Aggie
 
If you are a real TEXAS aggie, since I am a "Tea Sipper", maybe I shouldn't
help but.

Right click sheet tab>view code>copy/paste this.modify to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
On Error GoTo nono
With Sheets("destinationsheetname")
lr = .Cells(Rows.Count, "a").End(xlUp).row + 1
If UCase(Target) = "COMPLETED" Then _
Rows(Target.row).Copy Destination:=.Rows(lr)
Rows(Target.row).Delete
End With
nono:
End Sub
 
Assume the first sheet (source) is called "s1"
Assume the second sheet (destination) is called "s2"
Assume the status column in s1 is column A:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
If Target.Value <> "complete" Then
Exit Sub
End If

Set r1 = Target.EntireRow
Set r2 = Sheets("s2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
r1.Copy r2
Application.EnableEvents = False
r1.Delete
Application.EnableEvents = True
End Sub

This is worksheet code, it does not go in a standard module.
 
I was deleting row anyway so change to

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
On Error GoTo nono
With Sheets("yyy")
lr = .Cells(Rows.Count, "a").End(xlUp).row + 1
If UCase(Target) = "COMPLETED" Then
Rows(Target.row).Copy Destination:=.Rows(lr)
Rows(Target.row).Delete
End If
End With
nono:
End Sub
 
Back
Top