Microsoft Excel - Excel - Automatic Moving of row to another worksheet based on cell contents

Hi,

I am fairly new to Excel programming but have done quite a lot of VBA in the past. I have a spreadsheet with two Worksheets - "Open Work" and "Completed Work".

When I change one of the cells in Open Work to a Priority 4 I would like that entire row cut and pasted to a row in the Completed Work worksheet. I have looked around on other forums and believe this is possible but I am pulling my hair out trying to get it to work!

Thanks to anyone who can help.

Kind Regards,

Colin

Hi Colin:

if I am correctly assuming that you have a column named 'Priority' in your 'Open Work' worksheet you could place the following event handler into the VBA module associated with your 'Open Work' worksheet and give it a try (after you adjusted the constant PRIORITY_COLUMN to your situation):

Const PRIORITY_COLUMN As String = "B"
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> Columns(PRIORITY_COLUMN).Column Then Exit Sub
    If Target.Value = 4 Then
        Rows(Target.Row).Copy Worksheets("Completed Work").Range("A" & Rows.Count).End(xlUp).Offset(1)
        Rows(Target.Row).Delete Shift:=xlUp
    End If
End Sub

Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm