automating a spreadsheet

--------------------------------------------------------------------------------

Hi

I have created table of jobs my company has to do for a certain customer. Some are complete and some are incomplete.

Currently the incomplete jobs are entered in the 'Incomplete' sheet, once they are completed they are cut and paste to the 'Complete' sheet. Incomplete and complete jobs are defined in the 'Invoiced' Coulmnm as "No' or anything apart from 'no' (as a job is not fully complte until it has been invoiced).

I understand that it may be easier so sort with a new column for just; Complete - Yes or No, which is no problem to change.

The big issue is that I would like to automate excel so that once a job is marked as complete excel automatically removes it form the "incomplete" list and put it in the "complete' list in a seperate sheet.


A small sample workbook has been provided

It may also be important to note that this is only a sample, there are over 4000 jobs in the full workbook.

Thank you very much for any help

Have a look at the attached.

It uses an event driven macro to make the transfer.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Application.EnableEvents = False
For Each Cell In Target
    If Cell.Column = 8 And Cell.Row > 1 Then
        If Cell = "Yes" Then
            Cell.EntireRow.Copy Destination:=Sheets("Complete").Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
            Cell.EntireRow.Delete
        End If
    End If
Next Cell
Application.EnableEvents = True
End Sub