Move Completed cells auto using VBA when they have been changed to completed

[Clo112358]

I am trying to move cells on an excel document from FDJ- TIP to Completed TIP.

I used this in module:

Sub MoveCompletedRows()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lastRow As Long
    Dim i As Long

    ' Set your source and destination sheets
    Set wsSource = ThisWorkbook.Sheets("SourceSheet")
    Set wsDestination = ThisWorkbook.Sheets("DestinationSheet")

    ' Find the last row with data in the source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

    ' Loop through each row from the bottom up
    For i = lastRow To 1 Step -1
        If wsSource.Cells(i, 6).Value = "Completed" Then ' Check if column F (6) is "Completed"
            wsSource.Rows(i).Copy Destination:=wsDestination.Rows(wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row + 1)
            wsSource.Rows(i).Delete
        End If
    Next i
End Sub

And this in the tab sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("F:F")) Is Nothing Then
        Call MoveCompletedRows
    End If
End Sub

It worked on a test sheet and then when applied to the live document it stopped working and wont move the row

 

[ajm]

looks ok to me. make sure your data occupies column A on which you base your last row count. also, make sure that you are making a change in column F which will trigger your macro. i don't think pasting new data in is enough to trigger a macro. changing the value of an existing cell and pressing enter should do it though. if it worked on your test sheet, check that you are doing everything the same. Create a separate Sub like this: Sub ee(): Application.EnableEvents = True: End Sub, then press F5 and try yours again. just served dinner. will test what you have provided above after i have eaten and see what happens. sadly, i find myself in similar situations now and again. best of luck.

 

[dmt32]

It worked on a test sheet and then when applied to the live document it stopped working and wont move the row

Hi welcome to forum

How are you changing value(s) in Column F of your source sheet - direct input or by formula?

Dave

 

[ajm]

Code works fine. You have to actually enter "Completed" (case sensitive) into column F.

 

[Clo112358]

Hi welcome to forum

How are you changing value(s) in Column F of your source sheet - direct input or by formula?

Dave

list with data validation

 

[dmt32]

As you stated your code did work once then trying running this code from a STANDARD module & then try your code again

Sub Reset()
 Application.EnableEvents = True
End Sub

Dave