Move a row to another sheet based on data manually changed - Help - on the verge of tears

[buxinsix]

I found a code on another post that does almost exactly what I want my spreadsheet to do, but I’ve tried changing the code to align with my sheet and I somehow mess it up.

On a sheet called “Contact Log” if I change column H to “Active Client” (manually) I want it to automatically copy that row to a spreadsheet called “Lead Status” I do not want it to delete the row. I want it to copy the entire row to column A, row 2 and on.

I’ve tried changing this code and it almost works. The first time I change a cell to active client it’ll copy the row, but it copies the data to column N, stops after the first few data changes, or it copies each time but only to column N, row 2, which means it continuously replaces the data.

Private Sub Worksheet_Change(ByVal Target As Range)

' Check to see only one cell updated
If Target.CountLarge > 1 Then Exit Sub

' Check to see if entry is made in column B after row 5 and is set to "Yes"
If Target.Column = 2 And Target.Row > 5 And Target.Value = "Yes" Then
Application.EnableEvents = False
' Copy columns B to I to complete sheet in next available row
Range(Cells(Target.Row, "B"), Cells(Target.Row, "I")).Copy Sheets("Complete").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
' Delete current row after copied
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End Sub

Automatically Move Row to Another Sheet Based On Cell Value

Hi all, Complete Excel and VBA newbie here, I'm trying to urgently find a solution to a VBA macro(?) that I need to create for work. Full disclosure, I have been trying to do this entirely via Googling and platforms like this amazing one! I'm trying to AUTOMATICALLY move a row of data from one...

www.mrexcel.com

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab you want row copied from
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/6/2022  10:38:00 PM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 8 Then
Application.ScreenUpdating = False
Dim ans As String
ans = "Lead Status"
Dim r As Long
Dim Lastrow As Long
r = Target.Row
    If Target.Value = "Active Client" Then
        Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Rows(r).Copy Sheets(ans).Cells(Lastrow, 1)
    End If
Application.ScreenUpdating = True

End If
End Sub

 

[buxinsix]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab you want row copied from
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/6/2022  10:38:00 PM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 8 Then
Application.ScreenUpdating = False
Dim ans As String
ans = "Lead Status"
Dim r As Long
Dim Lastrow As Long
r = Target.Row
    If Target.Value = "Active Client" Then
        Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Rows(r).Copy Sheets(ans).Cells(Lastrow, 1)
    End If
Application.ScreenUpdating = True

End If
End Sub

It worked! Thank you so freakin much.

 

[My Aswer Is This]

It worked! Thank you so freakin much.

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.