if condition true then move a whole row to another sheet
 

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

i would be grateful if anyone could help please and would prefer if there
wasnt 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

if condition true then move a whole row to another sheet
 

Hi

Place a button on the Order spreadsheet (to get button view toolbars
control toolbox and then draw a button where you want)

Now click Tools Macro visual basic editor

Double click the Order sheet and copy and paste the code below, Ive tested it
and it works fine, any problems email me and I shall send you my test which
works :-)



Sub commandbutton1_click()


lastroworder = Worksheets("Order").UsedRange.Row _
+ Worksheets("Order").UsedRange.Rows.Count - 1

lastrowpen = Worksheets("Pending").UsedRange.Row _
+ Worksheets("Pending").UsedRange.Rows.Count - 1

lastrowcom = Worksheets("Complete").UsedRange.Row _
+ Worksheets("Complete").UsedRange.Rows.Count - 1

For pen = 1 To lastroworder
If UCase(Worksheets("Order").Cells _
(pen, 8).Value) = UCase("Pending") Then
Worksheets("Pending").Range("A" & _
lastrowpen + 1 & ":H" & lastrowpen + 1).Value = _
Worksheets("Order").Range("A" & pen & ":H" & pen).Value
Rows(pen).ClearContents
lastrowpen = lastrowpen + 1
End If
Next pen

For com = 1 To lastroworder
If UCase(Worksheets("Order").Cells _
(com, 8).Value) = UCase("Complete") Then
Worksheets("Complete").Range("A" & _
lastrowcom + 1 & ":H" & lastrowcom + 1).Value = _
Worksheets("Order").Range("A" & com & ":H" & com).Value
Rows(com).ClearContents
lastrowcom = lastrowcom + 1
End If
Next com


For del = lastroworder To 1 Step -1
If Sheets("Order").Cells(del, 8).Value = "" Then
Rows(del).Delete
End If
Next del

End Sub

--
Message posted via http://www.officekb.com