VBA help to copy rows when key words are selected

steve400243

Board Regular
Joined
Sep 15, 2016
Messages
222
Hello, I use the below code to transfer rows from the "LOG" Tab to the "RTN HISTORY" tab when "SCRAP", or "RELEASED" in Col Z is selected from the drop down. My question is how would I have it transfer back to the LOG tab if "HOLD" is selected from the dropdown? Thanks for all help.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Z:Z")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("RTN HISTORY").Cells(Rows.Count, "Z").End(xlUp).Row + 1
    If Target.Value = "SCRAP" Then
        Rows(Target.Row).Copy Destination:=Sheets("RTN HISTORY").Rows(Lastrow)
        Application.EnableEvents = False
        Rows(Target.Row).Delete
        Application.EnableEvents = True
        Exit Sub
    End If
Application.EnableEvents = True
    If Target.Value = "RELEASED" Then
        Rows(Target.Row).Copy Destination:=Sheets("RTN HISTORY").Rows(Lastrow)
        Application.EnableEvents = False
        Rows(Target.Row).Delete
    End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
 

steve400243

Board Regular
Joined
Sep 15, 2016
Messages
222
Never Mind all, it was easier than I thought once I slowed down and thought about it..

Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("Z:Z")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("LOG").Cells(Rows.Count, "Z").End(xlUp).Row + 1
    If Target.Value = "HOLD" Then
        Rows(Target.Row).Copy Destination:=Sheets("LOG").Rows(Lastrow)
        Application.EnableEvents = False
        Rows(Target.Row).Delete
        Application.EnableEvents = True
        Exit Sub
    End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
 
Last edited:

Forum statistics

Threads
1,077,674
Messages
5,335,605
Members
399,028
Latest member
greyland

Some videos you may like

This Week's Hot Topics

Top