Move row to other sheet based on cell value change in specific column

Re: How to move a row in excel to a new worksheet?

You could try this post
http://www.ozgrid.com/forum/sh…p?t=147482&goto=newpost**

It contains two lots of code run from a button

It will give you a start.

Windy

Re: Move row to other sheet based on cell value change in specific column

On a backup copy of the target workbook, right click the tab of the target worksheet and paste this code into the VB Editor.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)


    Dim lngRow As Long, ws As Worksheet, nextrow As Long
        
    If Target.Cells.Count > 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    
    If Not Intersect(Target, Columns("M:M")) Is Nothing Then
        If Target.Value = "Completed" Then
            lngRow = Target.Row
            On Error Resume Next
            With ThisWorkbook
                Set ws = Worksheets("Completed")
                If ws Is Nothing Then .Worksheets.Add().Name = "Completed"
                nextrow = Worksheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row + 1
            End With
            With Sheet1 'code name
                .Range("A" & lngRow & ":M" & lngRow).Copy Destination:=Worksheets("Completed").Range("A" & nextrow)
                .Range("A" & lngRow).EntireRow.Delete shift:=xlUp
            End With
        End If
    End If
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Set ws = Nothing
    
End Sub

This code tests for the existence of the "Completed" worksheet and creates it if it does not exist.
Each time the value in cells in column-M are changed to "Completed" the code will copy cells A-M of the active row to the sheet, then delete the row, shift rows up.

Change any sheet names and reference as required.

Re: Move row to other sheet based on cell value change in specific column

Can this be done for multiple values?

Re: Move row to other sheet based on cell value change in specific column

Hi jordanh,
Welcome to the forum.
This is a very old thread. Best to start a new thread and reference this one if the code is relevant!

Re: Move row to other sheet based on cell value change in specific column

Thanks
Will do...