help with automating a spreadsheet please!!

[manaz]

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

 

[manaz]

sorry here is the link for the file:

http://www.megaupload.com/?d=AO296CRS

 

[VoG]

Hello and welcome to MrExcel.

Try this: right click the Incomplete sheet's tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
If Target.Column = 9 Then
    If LCase(Target.Value) = "yes" Then
        Application.EnableEvents = False
        LR = Sheets("Complete").Range("A" & Rows.Count).End(xlUp).Row
        With Target.EntireRow
            .Copy Destination:=Sheets("Complete").Range("A" & LR + 1)
            .Delete
        End With
        Application.EnableEvents = True
    End If
End If
End Sub

For this to work you will need to enable macros and save the file as an .xlsm file (Excel Macro-Enabled Workbook).

 

[texasalynn]

ok so I'm slow, but here is what I came up with

Private Sub Worksheet_Change(ByVal Target As Range)
AC = Target.Address
Application.EnableEvents = False
 If Range(AC) = "Yes" Then
    LR = Sheets("Incomplete").Cells(Rows.Count, 1).End(xlUp).Row + 1
    Range(AC).EntireRow.Cut Worksheets("Complete").Range("A" & LR)
    Range(AC).EntireRow.Delete
End If
Application.EnableEvents = True

End Sub

this code goes in the Incomplete sheet - so right click on the tab name and click view code. Paste the above code

HTH

 

[manaz]

you guys are great thank you!

 

[manaz]

thanks guys, i have run into a new problem.

The code above works, but when i use a form to edit the invoices in the 'incomplete' sheet i.e. change the 'invoiced' column from 'no' to 'yes' the record isnt moved to the 'complete' sheet.

Can anyone help with this?

 

[VoG]

when i use a form to edit the invoices in the 'incomplete' sheet i.e. change the 'invoiced' column from 'no' to 'yes'

Can you explain what you mean in more detail?

 

[manaz]

sorry, i have the form button added to the quick access toolbar. I use that to enter the new jobs in the 'incomplete' sheet. Then again to find a specific job and mark it ac complete. How ever when i mark the job off it is not moved to the 'complete' sheet.

does that help?

 

[VoG]

I don't see why that would affect things ... does the code still run correctly if you mark a job as complete manually?

 

[manaz]

yes, its wierd