Hello Everyone. I was tasked to create a spreadsheet that can update ranges to a new worksheet when column E (or 5) is updated to "Denied" or "Scheduled"
When denied it will go to sheet2 and when it is schedule it will go to sheet3
I was successful in getting "Denied" to work and move the range over successfully, but when writing the code based of the "Denied" code I learned that you have to merge the code as there cannot be two worksheet changes.
This is the code I have so far just need tips or instructions on how to merge the code.
Code
`Macro 1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 5 Then Exit Sub
If UCase(Target) <> "DENIED" Then Exit Sub
Dim i As Long, last As Long
Z = Target.Row
last = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheet2.Range("A" & last & ":i" & last).Value = Sheet4.Range("A" & Z & ":i" & Z).Value
Sheet4.Rows(Z & ":" & Z).Delete Shift:=xlUp
`Macro 2
If Target.CountLarge > 1
Then Exit Sub
If Target.Column <> 5
Then Exit Sub
If UCase(Target) <> "Scheduled" Then Exit Sub
Dim i As Long, last As Long
Z = Target.Row
last = Sheet3.Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheet3.Range("A" & last & ":i" & last).Value = Sheet4.Range("A" & Z & ":i" & Z).Value
Sheet4.Rows(Z & ":" & Z).Delete Shift:=xlUp
End Sub
Display More