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!

 

[Mposwal]

Try the logic below and make required changes if you want:

Dim currRow As Long
Private Sub Worksheet_Change(ByVal Target As Range)
currRow = ActiveCell.Row - 1
Sheets("Sheet1").Rows(currRow).Copy Sheets("Sheet2").Rows(currRow)
End Sub

 

[Daleksec93]

Try the logic below and make required changes if you want:

Dim currRow As Long
Private Sub Worksheet_Change(ByVal Target As Range)
currRow = ActiveCell.Row - 1
Sheets("Sheet1").Rows(currRow).Copy Sheets("Sheet2").Rows(currRow)
End Sub

this will move them based on adding a "Z" to column A automatically?

 

[Mposwal]

Type Z and press Enter, it will move

 

[Mposwal]

Put the code in the sheet where you are typing (Sheet1 in this case)

 

[Daleksec93]

Type Z and press Enter, it will move

it does not work. The other ones i saw and looked through i changed proper information and it still not work. there was more to those than this though. it does not show which column is being targeted for change to Z and it does not show automatically script

 

[Mposwal]

Where are u putting the code?

 

[Daleksec93]

THE MODULES OF COURSE

 

[Daleksec93]

THE MODULES OF COURSE

I have columns A-AP that need to moved over with it. so the entire row goes to other sheet in the first available row when Z is entered in coloumn A starting with row 3 row 2 is header

 

[kevin9999]

Try this. Right click on the sheet tab name for the sheet "CURRENT", select View Code, copy the code below to the window that appears on the right of the screen. Save the file & test it by typing a "Z" in column A of the "CURRENT sheet. As always, test this with a copy of your file.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A4", Cells(Rows.Count, "A").End(xlUp)), Target) Is Nothing Then
    If Target.Value <> "Z" Then Exit Sub
    Dim lRow As Long
    lRow = Sheets("REMOVED").Cells.Find("*", , xlFormulas, , 1, 2).Row + 1
    With Target.EntireRow
        .Copy Sheets("REMOVED").Cells(lRow, 1)
        .Delete xlUp
    End With
    End If
End Sub