Need VBA Code to move a row to another sheet and keep row formatting.

[jlaw1]

Hello,
I'm struggling to work out the correct VBA code to move a row in a table to the bottom open row of another sheet, deleting the row in the first sheet but still keeping the row formatting.
I have column C which is a sales stage and once it's updated to a specific stage (Won) from a validation drop down, I need that entire row to move to the Won sheet on the bottom open row while keeping the row formatting.

 

[Peter_SSs]

Welcome to the MrExcel board!

What code are you currently using?

 

[jlaw1]

Below is the code that I'm working with but it's also below another Sub so I'm able to get it to work correctly without errors

Sub MoveWonStage(ByVal Target As Range)
'move row based on sales stage (won)

    If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Target = "Won" Then
        Target.EntireRow.Copy Sheets("In Process-Won").Cells(Sheets("In Process-Won").Rows.Count, "A").End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

Try this Worksheet_Change event code. To implement ..
1. Right click the sheet name tab of the sheet with the drop-downs in column C and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Target.Column = 3 And Target.Value = "Won" Then
      Application.EnableEvents = False
      Target.EntireRow.Copy Sheets("In Process-Won").Cells(Sheets("In Process-Won").Rows.Count, "A").End(xlUp).Offset(1)
      Target.EntireRow.Delete
      Application.EnableEvents = True
    End If
  End If
End Sub