if column H contains 'complete' then move row

[news.btconnect.com]

I have an excel workbook which contains 3 worksheets - Order, Pending &
Complete - each sheet is exactly the same layout and header row
i would like to automate the following:
if on the Order sheet "Complete" is entered in column H then move whole row
to end of Complete sheet
or if 'Pending' is entered in column H then move whole row to Pending sheet
and also if 'Complete' is subsequently entered into column H of Pending
sheet then move whole row to end of Complete sheet

i would be grateful if anyone could help please and would prefer if there
were not any formulae on the actual sheets as due to the way stuff is
entered on the Order sheet any formulae could easily be overwritten

thankyou
xx Gran2

 

[flummi]

Hi,

If this is still an issue then here's a raw proposal.

There are a couple of issues though like

- you can change the status of and order to "pending" on the order
sheet and then change to "complete" on the pending sheet. However, it's
still "pending" on the order sheet. The procedure below accepts
"pending" and "complete" on the order sheet and "complete" on the
pending sheet . But that could still leave an order "pending" on the
pending sheet.

So, depending on what you plan to do next the procedure below needs
adapting.

Hans


Sheets: "Orders", "Pending" and "Complete".

Orders:

order value status
4525 5221 pending
152 5221 complete
124 315 pending

Pending:

4525 5221 complete
124 315 complete

Complete:

152 5221 complete
124 315 complete
4525 5221 complete


VBA code for the Change event of sheet "Pending":

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
If Target.Column = 3 Then
If UCase(Cells(Target.Row, Target.Column)) = "COMPLETE" Then
shName = "complete"
Else
Exit Sub
End If

EmptyRow = Sheets(shName).Cells(Rows.Count, "a").End(xlUp).Row
+ 1
arow = Target.Row
acol = Target.Column
Rows(Target.Row).Select
Selection.Copy
Sheets(shName).Activate
ActiveSheet.Range("A" + CStr(EmptyRow)).Select
ActiveSheet.Paste
Sheets("Pending").Activate
ActiveSheet.Cells(arow, acol).Activate
End If
End Sub

VBA code for the change event of sheet "Orders":

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
If Target.Column = 3 Then
If UCase(Cells(Target.Row, Target.Column)) = "COMPLETE" Then
shName = "complete"
Else
If UCase(Cells(Target.Row, Target.Column)) = "PENDING" Then
shName = "Pending"
Else
Exit Sub
End If
End If

EmptyRow = Sheets(shName).Cells(Rows.Count, "a").End(xlUp).Row
+ 1
arow = Target.Row
acol = Target.Column
Rows(Target.Row).Select
Selection.Copy
Sheets(shName).Activate
ActiveSheet.Range("A" + CStr(EmptyRow)).Select
ActiveSheet.Paste
Sheets("orders").Activate
ActiveSheet.Cells(arow, acol).Activate
End Sub

 

[Gran2]

Thankyou very much for replying - i will give it a try
Regards
Gran2