Macros need whole line moved to new worksheet automatically

[aboyal]

I want to create a macro that when date is inserted into certain cell, have the whole line moved to a new worksheet, and re move from original sheet.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
attempted to create the formula, but insufficient excel knowledge,<o></o>
<o> </o>
<o> </o>
needs whole line moved to new worksheet if column "i" contains<o></o>
<o> </o>
-------------------------<o></o>
needs a macro that will<o></o>
1. check the completed date cell for data<o></o>
2. IF the data is present, then COPY the data in the row from one sheet to another<o></o>
3. THEN delete the data from the original sheet<o></o>
4. LOOP through and go to the next cell to check<o></o>
5. Variables would have to be used<o></o>
<o> </o>
<o>Any guidence would be super appriciated </o>
<o>Thank you in advance! </o>

 

[Joe4]

OK, I assumed that you want it happen "on the fly", as dates are being manually typed into column E.
I also assumed that you want the blank row to be removed so that all other rows below move up one.
And your destination sheet ("Completed jobs") starts pasting data in the first blank row in column A and keeps moving down.

So here is the code that I came up with. Right click on the sheet tab name of the sheet you are typing the dates into, select "View Code", and paste the following code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)
 
'   Check to see if a date has been added to column E
    If Target.Column = 5 And IsDate(Target.Value) And Target.Count = 1 Then
        Application.EnableEvents = False
        Call MyMove(Target.Row)
        Application.EnableEvents = True
    End If
    
End Sub
 
Private Sub MyMove(myRow As Long)
 
    Dim myOrigSheet As Worksheet
    Dim myDestSheet As Worksheet
    Dim myLastRow As Long
    Dim i As Long
    
'   Set sheets
    Set myOrigSheet = ActiveSheet
    Set myDestSheet = Sheets("Completed jobs")
    
'   Find last row on destination sheet
    If Len(myDestSheet.Cells(1, "A")) = 0 Then
        myLastRow = 0
    Else
        myLastRow = myDestSheet.Cells(Rows.Count, "A").End(xlUp).Row
    End If
    
'   Copy column A-H of row to new sheet
    For i = 1 To 8
        myDestSheet.Cells(myLastRow + 1, i) = myOrigSheet.Cells(myRow, i)
    Next i
    
'   Delete original row
    myOrigSheet.Rows(myRow).EntireRow.Delete
 
End Sub

Then as long as you have macros enabled, this code should run automatically and do what you want.

 

[aboyal]

(immediately upon entering data in this column)
YES column E

When the rows of data is removed from your original sheet, do you want the blank row deleted so that all other rows move up one?
YES pls

When the data is pasted into your "Completed jobs" sheet, do you want to start in row 1, and then put each succeeding row after that?
YES preferably after all content that is allready there if not as long as it goes to that list that would be great.

1 more thing that might need to be added
the work book has tabs at the bottom so 5 or 6 different lists would it be possible to move the row from one list to the exact row into the completed work book list if that makes sence? or do i just use the formula on each sheet?

 

[Joe4]

1 more thing that might need to be added
the work book has tabs at the bottom so 5 or 6 different lists would it be possible to move the row from one list to the exact row into the completed work book list if that makes sence? or do i just use the formula on each sheet?

Not sure I understand. Maybe an example would clarify it.