VBA: Move entire row if date is entered in a cell

[walker2011au]

Hi Guys,

Got an Excel 2007 workbook with two sheets 'tasks' & 'completed'

Each has 3 columns with data under them starting at A1

Task | Detail | Completion Date




all I need done is when a date is entered under 'completion date' the entire row is cut from 'tasks' sheet and pasted on 'completed' sheet (without overriding other completed tasks)

Cheers

 

[Andrew Poulsom]

What did you try? You can use the Worksheet_Change event procedure, although I would find it pretty disconcerting if my data vanished when I entered something.

 

[walker2011au]

What did you try? You can use the Worksheet_Change event procedure, although I would find it pretty disconcerting if my data vanished when I entered something.

I am a VBA noob, played around with the following code I was sent and didnt get far

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 1 Then Exit Sub Application.EnableEvents = False If LCase(Target) = "closed" Then With Sheets("sheet8") lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Target.EntireRow.Copy Destination:=.Cells(lr, 1) Target.EntireRow.Delete End With End If Application.EnableEvents = True

Though that code is designed for 'Closed' to initiate the move not a date, looks like it copies it rather then cuts it too..

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NextRow As Long
    If Target.Column <> 3 Then Exit Sub
    Application.EnableEvents = False
    With Sheets("sheet8")
        NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        Target.EntireRow.Copy Destination:=.Cells(NextRow, 1)
        Target.EntireRow.Delete
    End With
    Application.EnableEvents = True
End Sub

 

[walker2011au]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NextRow As Long
    If Target.Column <> 3 Then Exit Sub
    Application.EnableEvents = False
    With Sheets("sheet8")
        NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        Target.EntireRow.Copy Destination:=.Cells(NextRow, 1)
        Target.EntireRow.Delete
    End With
    Application.EnableEvents = True
End Sub

thanks will have a play, so sheet8 i would change to 'tasks', where does it reference my other sheet which is 'completed'

 

[Andrew Poulsom]

Column C (3).

 

[walker2011au]

Column C (3).

I am really noob and cant figure it out, current code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextRow As Long
If Target.Column <> 3 Then Exit Sub
Application.EnableEvents = False
With Sheets("tasks")
NextRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
Target.EntireRow.Copy Destination:=.Cells(NextRow, 1)
Target.EntireRow.Delete
End With
Application.EnableEvents = True
End Sub

where do I reference "completion date" sheet? you mention column C but I don't see that in the code.

I inserted the code into "thisworkbook" is that right?

 

[JLGWhiz]

I believe the code is intended to be put into the code module of your sheet where you enter the date and the reference to column C is where he has If Target.Column <> 3. The number 3 is equivalent to column C in this case. To insert the code, click on the sheet name tab for that sheet, then click "View Code" in the pop-up menu. Whent the VB Editor screen appears, copy the code into that window. After that, when you enter a date in column C, it will activate the code.

 

[walker2011au]

I believe the code is intended to be put into the code module of your sheet where you enter the date and the reference to column C is where he has If Target.Column <> 3. The number 3 is equivalent to column C in this case. To insert the code, click on the sheet name tab for that sheet, then click "View Code" in the pop-up menu. Whent the VB Editor screen appears, copy the code into that window. After that, when you enter a date in column C, it will activate the code.

thanks done that, didnt do a thing when date entered.