MAKE MACRO/VBA TO MOVE ROW TO ANOTHER SPREADSHEET AND DELETE OLD ROW WHEN DATE AENTERED IN A CELL

[Daleksec93]

Hi Experts!

I have had a go at this myself and have only been able to do make this work when i initially put the code in. Does not continue to work there after.

I am trying to move a row to another sheet if I type "Z" in the "Job completed?" Column (A3-E218.) after row 3. row 3 is title on both sheet. sheet 1 is called CURRENT and sheet 2 is called REMOVED

I need this to happen instantly after typing "Z" in column A starting with row 4

Please let me know if there is any other information you will need to be able to help me with this.

Thank you!

 

[Daleksec93]

OK, try the code in post #18 and let me know how you go.

The code send the z'd out row to the other sheet but not in aphabetical order

 

[kevin9999]

The code send the z'd out row to the other sheet but not in aphabetical order

Just so we're clear - you want the rows from the sheet "CURRENT" (once moved to sheet "REMOVED") to be sorted on column B on the sheet "REMOVED" as well?

 

[kevin9999]

This will be my last post for today on this thread. I've added a line that will sort the moved rows on the "REMOVED" sheet once they're there.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("CURRENT")
    Set ws2 = Worksheets("REMOVED")
    Dim LRowCUR As Long, LRowREM As Long
    LRowCUR = ws1.Cells.Find("*", , xlFormulas, , 1, 2).Row
    LRowREM = ws2.Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
   
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A4:B" & LRowCUR), Target) Is Nothing Then
        Select Case Target.Column
            Case Is = 1
                If Target.Value = "Z" Then
                    Application.EnableEvents = False
                    With Target.EntireRow
                        .Copy ws2.Cells(LRowREM, 1)
                        .Delete xlUp
                    End With
                    ws2.Range("B3").CurrentRegion.Sort Key1:=ws2.Range("B3"), order1:=xlAscending, Header:=xlYes
                End If
            Case Is = 2
                Application.EnableEvents = False
                ws1.Range("B3").CurrentRegion.Sort Key1:=ws1.Range("B3"), order1:=xlAscending, Header:=xlYes
        End Select
    End If
    Application.EnableEvents = True
End Sub

 

[Daleksec93]

Just so we're clear - you want the rows from the sheet "CURRENT" (once moved to sheet "REMOVED") to be sorted on column B on the sheet "REMOVED" as well?

Yes both sheets are to be sorted by the column B header in Row 2. So the information on "Current" is already sorted even before some rows are sent over to "Removed." and "Removed with be sorted as it is sent over.