#1
|
|||
|
|||
Two macros in one worksheet
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, |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |