Auto Move Rows macro is deleting needed data

Greetings,

This is my first time posting. I originally joined due to some great help I received here in recording a macro for my RACI matrix (original post here), which moved each row in my table once that project's status was "complete" by deleting it and placing in it the first empty row in Sheet2. I modified the macro to fit my purposes, but now instead of pasting the row in the first empty space, it pastes it in the same row each time, deleting the last completed project. If someone is willing to tell me what I'm doing wrong and how to fix what I messed up in the code, I would be very grateful. I am very much a novice at this--this is only the second time I've ever used VBA! I've included both my file and my code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim nextrow As Long, i As Long
    
    nextrow = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 2
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    If Not Intersect(Target, Range("P2:P" & Cells(Rows.Count, "P").End(xlUp).Row)) Is Nothing Then
        i = Target.Row
        If Target.Value = "Complete" Then
            Range(Cells(i, "A"), Cells(i, "I")).Copy Sheet2.Range("A" & nextrow)
            Range("A" & Target.Row).EntireRow.Delete
        End If
    End If
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

JessicaB Project List.xlsm

Thank you very much!
Jessica

Hi

Your variable nextrow is being filled based on column A. However, you don't have any data in column A so it will keep going to the same next row. Try basing it on column B and see how that goes.

nextrow = Sheet2.Cells(Rows.Count, "B").End(xlUp).Row + 2

rylo

Aha! That makes sense! Your correction worked perfectly. Thank you, rylo!