Two macros in one worksheet

meowmeow · #1 11-01-2017, 03:42 AM

Hi all,

I'm having some issue with adding extra layers of complexity to a macro code.

Orginally,

I have been using a macro to move and delete 'completed tasks' from a Open tab to a Closed tab. The macro is here:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 12 Then
If Target = "Yes" Then

Application.EnableEvents = False
nxtRow = Sheets("Closed Issues").Range("L" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Closed Issues").Range("A" & nxtRow)
Target.EntireRow.Delete

End If
End If
Application.EnableEvents = True
End Sub

However, I want to develop it further and repeat this macro, adding a new function to move to a different sheet depending on cell value. Like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 12 Then
If Target = "Yes" Then

Application.EnableEvents = False
nxtRow = Sheets("Closed Issues").Range("L" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Closed Issues").Range("A" & nxtRow)
Target.EntireRow.Delete

End If
End If

If Target.Column = 13 Then

If Target = "Low" Then

Application.EnableEvents = False
nxtRow = Sheets("Low Priority Issues").Range("M" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy _
Destination:=Sheets("Low Priority Issues").Range("A" & nxtRow)
Target.EntireRow.Delete

End If
End If
Application.EnableEvents = True
End Sub

When I do this repeat, it comes up with an error saying "Object required", the both set of macros stop working and I'm unsure how I can get both of this functions working together.

Any advice/help would be super appreciated.

Thanks,

NoSparks · #2 11-01-2017, 05:13 AM

I suspect the 'Object Required' error is on the If Target.Column = 13 line and is happening because the If Target.Column =12 part has just deleted the target row,
so that line is looking for a target that no longer exists because its just been deleted.

Try this way

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim nxtRow As Long

' limit to a single cell change in columns 12 or 13
If Target.Count > 1 Then Exit Sub
If Target.Column < 12 Or Target.Column > 13 Then Exit Sub

' if column 12
If Target.Column = 12 And UCase(Target.Value) = "YES" Then
    nxtRow = Sheets("Closed Issues").Range("L" & Rows.Count).End(xlUp).Row + 1
    Target.EntireRow.Copy _
    Destination:=Sheets("Closed Issues").Range("A" & nxtRow)
    Application.EnableEvents = False
    Target.EntireRow.Delete
    Application.EnableEvents = True
    Exit Sub
End If

' if column 13
If Target.Column = 13 And UCase(Target.Value) = "LOW" Then
    nxtRow = Sheets("Low Priority Issues").Range("M" & Rows.Count).End(xlUp).Row + 1
    Target.EntireRow.Copy _
    Destination:=Sheets("Low Priority Issues").Range("A" & nxtRow)
    Application.EnableEvents = False
    Target.EntireRow.Delete
    Application.EnableEvents = True
    Exit Sub
End If
End Sub

meowmeow · #3 11-01-2017, 06:19 AM

NoSparks,

Thank you so much for helping me out with this, and such a quick response too! I have just tried what you have written and it is working great.

I really appreciate it

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Problems with protected worksheet and macros	JohnGanymede	Excel Programming	7	07-20-2017 04:55 PM
How to automatically enable the macros upon opening a file with macros?	laurieli	Office	7	01-17-2016 08:56 AM
Appending unique data from one worksheet to existing data on another worksheet	EdStockton	Excel	1	08-06-2014 11:00 PM
Using macros to highlight worksheet words referenced in external list/doc.	Daniel_NYC	Word VBA	1	04-21-2014 03:35 PM
How to summarise different worksheet to a summary worksheet	samkiewhock	Excel	1	09-06-2012 03:34 AM