Microsoft Excel 2007 Auto Move Rows

Question

Hello all,

I am creating a task tracker and I am trying to figure out how to automatically move rows of completed tasks into a seperate sheet.  I know I will have to use a macro but I have never used one before.  The column containing the "Completed" status is column I and I would like to move the rows into sheet 2.  Any help is appreciated!

Jeff

Answer 1

Right-click on Sheet1 tab and "View Code" to open module.

Copy this code into Sheet1 module and when you enter Completed in column I that row will be cut and moved to next available row in Sheet2

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

On Error GoTo enditall

    Application.EnableEvents = False

If Target.Cells.Column = 9 Then

        n = Target.Row

        If Me.Range("I" & n).Value = "Completed" Then

          Me.Range("A" & n & ":" & "I" & n).Cut _

           Destination:=Sheets("Sheet2").Cells _

           (Rows.Count, 9).End(xlUp).Offset(1, 0)  'edited  (rows.count, 1)  to 9

        End If

    End If

enditall:

    Application.EnableEvents = True

End Sub

Gord

Answer 2

So this code worked, but what if you have multiple?

I have a log I use for Change Orders, and in column "k" I have 5 selections in the drop list, 2 of those 5 I would like to transfer to a new sheet.  below code is what I used/modified that works for all of my completed (5-Completed), but I also want for rejected lines to do the same (4-Rejected).  Any help would be great.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

 On Error GoTo enditall

Application.EnableEvents = False

 If Target.Cells.Column = 11 Then

n = Target.Row

If Me.Range("K" & n).Value = "5-Completed" Then

Me.Range("A" & n & ":" & "P" & n).Cut _

Destination:=Sheets("Completed").Cells _

(Rows.Count, 1).End(xlUp).Offset(1, 0)  'edited  (rows.count, 1)  to 16

End If

End If

enditall:

Application.EnableEvents = True

 End Sub