Move Data between sheets Automatically off of cell Value

[jesuisazep]

Hello All!

Gonna try to ask for help as I thought I had figured it out on my own to no luck.
Would love a macro to accomplish The problem:
In the Sheet Below I want two things (if Possible):
In column N You can enter either "Complete" or "Incomplete"
Should you Enter "Incomplete" I want the entire row copied over to the sheet title "SI" and deleted from the current sheet
Should you enter "Complete" I want the information in columns C though J copied over to the the sheet "FUF" but there is no need to delete this row

Thank you for assistance

 

[hicksi]

I presume that your user when reviewing this sheet will enter "Complete" into column N. And then...
If you make the move happen at the Cell-Exit stage, the sheet will update each time the user hits return. Which is highly disturbing as a user-experience, and difficult to ensure that the cursor moves to the correct next-row (ie, it's the same row, just with different data).
The best bet is to have the user enter "Complete" into all the complete rows, and then have everything move once some indication that the entries have been reviewed and checked has occurred.

SO...
In the Microsoft Excel Objects for sheet LF, create the following

Private Sub Worksheet_Change(ByVal tCell As Excel.Range)
    If tCell.Column = 14 And tCell.Row >= 8 Then
        If tCell.Value = "Complete" Then Exit Sub
        If tCell.Value = vbNullString Then Exit Sub
        If UCase(Trim(tCell.Value)) = Left("COMPLETE", Len(Trim(tCell.Value))) Then
            tCell.Value = "Complete"
        Else
            tCell.Value = vbNullString
        End If
    End If
End Sub

That will let the user enter anything like C, CO, COM, and all works great.

Now, to accurately MOVE the complete rows, someone needs to provide you with some whiz-bang code using AutoFilter, etc.
Personally, I would move them row by row.

 

[hicksi]

The code that you need is something like...
But I'm not sure exactly where to put this to make it automatic. I tried the WorkBook_BeforeSave but that screwed up the Save event.
Someone with better EVENT knowledge than me should complete...

Dim i               As Long
    If MsgBox("Are you sure that all the rows marked 'Complete' are actually completed and changes should be saved?", vbYesNo + vbDefaultButton2, "Confirm Save") = vbNo Then Cancel = True: Exit Sub
    For i = 8 To Sheets("LF").Range("A1").SpecialCells(xlLastCell).Row
        If Sheets("LF").Range("N" & i) = "Complete" Then
            Sheets("LF").Rows(i).Copy Sheets("SI").Range("A" & Sheets("SI").Range("A1").SpecialCells(xlLastCell).Row + 1)
            Sheets("LF").Rows(i).Delete
            i = i - 1                                               ' The Delete moves the rows up one
        End If
    Next