Excel-Move Rows to another sheet based on Cell Value

[Robertson1995]

Hello. I am needing help with code that will move specific rows to another sheet in the same workbook based on a Cell Value. In my workbook, I have a sheet named "Notes" that has values in Columns A-C. I need a code that when I enter the letter "X" in column D, it will copy the contents of A-C for that row and paste a copy on the first empty row in a sheet named "Note History". The copy will need to be the first empty row in Columns A-C for the Note History sheet. I also need the code to delete the entire row from the "Notes" sheet once it has copied the contents to the "Note History" sheet. Thank you in advance for any help.

 

[JLGWhiz]

Sounds like you need event code. Put this in your Sheet "Notes" workshe4et code module. It will run when you make a change on the sheet, but will only execute the copy when there is an X in column D.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns(4)) Is Nothing Then
    If UCase(Target.Value) = "X" Then
        Range("A" & Target.Row).Resize(1, 3).Copy Sheets("Note History").Cells(Rows.Count, 1).End(xlUp)(2)
        Target.EntireRow.Delete
    End If
End If
End Sub

 

[Robertson1995]

Doesn't seem to be working.

 

[Dryver14]

This works perfectly for me, you did put it on the notes sheet and not a module didn't you

 

[Robertson1995]

Sorry, I did put it in a module. Put it in the Notes sheet and works perfectly. Thank you so much for your help.

 

[JLGWhiz]

Sorry, I did put it in a module. Put it in the Notes sheet and works perfectly. Thank you so much for your help.

You're welcome,
regards, JLG

 

[nmrkumar]

Sounds like you need event code. Put this in your Sheet "Notes" workshe4et code module. It will run when you make a change on the sheet, but will only execute the copy when there is an X in column D.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns(4)) Is Nothing Then
    If UCase(Target.Value) = "X" Then
        Range("A" & Target.Row).Resize(1, 3).Copy Sheets("Note History").Cells(Rows.Count, 1).End(xlUp)(2)
        Target.EntireRow.Delete
    End If
End If
End Sub

Hi,
I am to new this forum and I don't have lot of experience in excel. I really appreciate any help.
I am try to do the similar thing. But I don't know where to paste the code. I don't know where to find the sheet "Notes".

I have attached the excel that I am working. When I type "CLOSE" in the column named "status" , I want the row "T1" moved to a different sheet named "Close". Please help. Thank You.

 

[nmrkumar]

Hi,
I am to new this forum and I don't have lot of experience in excel. I really appreciate any help.
I am try to do the similar thing. But I don't know where to paste the code. I don't know where to find the sheet "Notes".

I have attached the excel that I am working. When I type "CLOSE" in the column named "status" , I want the row "T1" moved to a different sheet named "Close". Please help. Thank You.

 

[Sharid]

Not sure if this will help, It work on moving 1 line at a time, Looks like you have merged cells so not sure if it will work

If column P has the word ACTIVE in it the code will ignore that row, anything else and it will move it to a Sheet called dormant

Private Sub CommandButton1_Click()

Dim LR As Long, NR As Long, a As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, "P").End(xlUp).Row
NR = Worksheets("Dormant").Range("P" & Rows.Count).End(xlUp).Offset(1).Row
For a = LR To 11 Step -1
'If column P has the word ACTIVE in it the code will ignore that row, anything else and it will move it to a Sheet called dormant
  If UCase(Range("P" & a)) <> "ACTIVE" And Range("P" & a) <> "" Then
  
'Moves Selected row to a sheet called Dorment
  Range("A" & a & ":CG" & a).Copy
Worksheets("Dormant").Range("A" & NR).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

    NR = Worksheets("Dormant").Range("P" & Rows.Count).End(xlUp).Offset(1).Row
    Worksheets("Dormant").Range("A" & NR & ":CG" & NR).Value = Range("A" & a & ":CG" & a).Value

'The row is then deleted, the button is on this sheet NOT Dormant Sheet
    Range("P" & a).EntireRow.Delete
  End If
Next a
Application.ScreenUpdating = True

End Sub

 

[nmrkumar]

Thank You Sharid. I don't want to use a command button. Can you please to make it automatic, in other words when status (Col G) is changed to "Closed" then the row should move to new sheet. Please help. Thanks