Hello All,
I have code that already works when I enter information into Column E and hit enter but what I want to happen is, when I copy multiple rows in Column E, change the date, and then hit enter, the selected rows all move into the new sheet.
So basically I want to be able to change the date in Column E row 1, then select the next couple rows in column E, or copy paste the same date in each one, and then when I press the Enter key the information will be moved over into the other sheet.
Also, I have 2 criteria to meet. Column G must have the "Not Due" in it and Column I must have "Week" in it. So if both criteria are meet and I enter the new date and select the next couple rows, after I hit the Enter Key, it gets moved over into the new sheet.
Below is the code I have so far. Again, this code works for moving one line at a time but does not work when I highlight multiple rows.
Any help is appreciated. Ryan
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E:E", "I:I")) Is Nothing Then
If Target.CountLarge > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Dim Lastrowa As Long
Application.ScreenUpdating = False
Lastrowa = Sheets("archive engagement").Cells(Rows.Count, "E").End(xlUp).Row + 1
Lastrow = Sheets("Week").Cells(Rows.Count, "E").End(xlUp).Row + 1
If Range("E" & Target.Row) <> "" And Range("G" & Target.Row) = "Not Due" And Range("I" & Target.Row) = "Week" Then
Rows(Target.Row).Copy Sheets("Week").Rows(Lastrow)
Application.EnableEvents = False
Rows(Target.Row).Delete
Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End If
End Sub
I have code that already works when I enter information into Column E and hit enter but what I want to happen is, when I copy multiple rows in Column E, change the date, and then hit enter, the selected rows all move into the new sheet.
So basically I want to be able to change the date in Column E row 1, then select the next couple rows in column E, or copy paste the same date in each one, and then when I press the Enter key the information will be moved over into the other sheet.
Also, I have 2 criteria to meet. Column G must have the "Not Due" in it and Column I must have "Week" in it. So if both criteria are meet and I enter the new date and select the next couple rows, after I hit the Enter Key, it gets moved over into the new sheet.
Below is the code I have so far. Again, this code works for moving one line at a time but does not work when I highlight multiple rows.
Any help is appreciated. Ryan
Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E:E", "I:I")) Is Nothing Then
If Target.CountLarge > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Dim Lastrowa As Long
Application.ScreenUpdating = False
Lastrowa = Sheets("archive engagement").Cells(Rows.Count, "E").End(xlUp).Row + 1
Lastrow = Sheets("Week").Cells(Rows.Count, "E").End(xlUp).Row + 1
If Range("E" & Target.Row) <> "" And Range("G" & Target.Row) = "Not Due" And Range("I" & Target.Row) = "Week" Then
Rows(Target.Row).Copy Sheets("Week").Rows(Lastrow)
Application.EnableEvents = False
Rows(Target.Row).Delete
Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End If
End Sub