Trying to Merge two worksheet_change(ByVal Target as Range)

J.Devine · Apr 21st 2022

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

**royUK** · Apr 21st 2022

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Apr 21st 2022

That is one procedure

J.Devine · Apr 21st 2022

Thank you royUK, I apologize for the issues. Thank you for the guidance.

**royUK** · Apr 21st 2022

Perhaps you want this

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long, last As Long
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub
    Z = Target.Row
    Select Case Target.Value
        Case "DENIED"
            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
        Case "Scheduled"
            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 Select
End Sub

Display More

I'm not sure exactly what your code is supposed to be doing, but it can probably be improved if you explain what you are doing.

J.Devine · Apr 21st 2022

The desire is to have the ability to update the status column and all of the information in that row is moved to a new sheet and deleted from the original sheet. There might be a much easier way than the code I initially started as I am definitely new at this.

**royUK** · Apr 21st 2022

To be honest, the best way is to keep it all in one sheet. You can then use AutoFilter to view only the selected status.

J.Devine · Apr 21st 2022

That might be the best case scenario. Thank you so much royUK. Just as a learning thing do you know the general rules for merging two spreadsheet changes? I appreciate your help!

Definitely sounds like I was just trying to over complicate and make it "flashy"

**royUK** · Apr 21st 2022

Your code will slow down inputting to the sheet because every change will force Excel to check the sheet.

If you really need to split the data (which is a very bad idea) then don't use an event code but run code to make the split after you have finished inputting.

**royUK** · Apr 21st 2022

There is no general rule for merging events. In your case I think my suggested code is the best way, but read my last post.

Trying to Merge two worksheet_change(ByVal Target as Range)

Participate now!

Share

Similar Threads

Optimise Multiple Worksheet_Change(ByVal Target As Range) - worksheet slows down as more rows added