Automatic Move
 

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

Automatic Move
 

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

Right click sheet tabview codecopy/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

--
Don Guillett
SalesAid Software

"Aggie G" wrote in message
...
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

Automatic Move
 

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.
--
Gary''s Student - gsnu200731

Automatic Move
 

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


--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
If you are a real TEXAS aggie, since I am a "Tea Sipper", maybe I
shouldn't help but.

Right click sheet tabview codecopy/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

--
Don Guillett
SalesAid Software

"Aggie G" wrote in message
...
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