Combine two worksheet change events

Hi gurus

Seeking help to combine two work sheet change events to avoid Ambiguous error.

The attached file has 3 macros in place.
1. To sort the contents in selected columns using a button
2. To allocate task to another workbook (Thanks Neeraj)
3. Auto populate date in column L (Thanks again Neeraj).

The second and third are both worksheet change event.
I want to remove the following section of the code from vba#3, to hide rows based on a criteria. This section is not needed in the attached spreadsheet. This was developed for another spreadsheet.
ElseIf Not Intersect(Target, Range("AA6:AA3000")) Is Nothing Then
    If Target <> "" Then
        If LCase(Target.Value) = "completed" Then
            Target.EntireRow.Hidden = True

Open in new window


After removing the section from third vba, can we combine the second and third macros, to overcome the ambiguous error alert.

Can you help please.
Team-2.xlsm
Matt MaliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think you forgot to upload the file.
0
Matt MaliAuthor Commented:
Thanks Neeraj for pointing out the error. The file is now uploaded into my initial request .
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi Matt,

Please delete both the existing Sheet Change Event Codes from the Task Allocation Sheet Module and place the following code in there.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Dim dws2 As Worksheet
    Dim dwb2 As Workbook
    Dim dwbPath As String, Team As String, TeamNo As String
    Dim r As Long
    
    Application.ScreenUpdating = False
    If Not Intersect(Target, Range("N6:N" & Range("C" & Rows.Count).End(xlUp).Row)) Is Nothing Then
        If Target <> "" Then
            TeamNo = ExtractNumber(Target.Value)
            dwbPath = "C:\Secured\Planner Level\Planner " & TeamNo & "\Planner " & TeamNo & ".xlsm"
        On Error Resume Next
        Set dwb2 = Workbooks("Planner " & TeamNo & ".xlsm")
        On Error GoTo 0
        
            If dwb2 Is Nothing And Dir(dwbPath) <> "" Then
                Set dwb2 = Workbooks.Open(dwbPath, False)
            End If
            If Not dwb2 Is Nothing Then
                Set dws2 = dwb2.Sheets(1)
                Range("C" & Target.Row & ":K" & Target.Row).Copy
                 dws2.Range("C" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
                End If
            Range("C" & Target.Row & ":O" & Target.Row).Delete shift:=xlUp
        End If
        Range("N6").Select
    ElseIf Not Intersect(Target, Range("C6:C" & Range("C" & Rows.Count).End(xlUp).Row)) Is Nothing Then
        r = Target.Row
        If Target <> "" Then
            Application.EnableEvents = False
            If Cells(r, "L") = "" Then
                Cells(r, "L").NumberFormat = "dd/mm/yyyy"
                Cells(r, "L") = Now
            End If
            Application.EnableEvents = True
        End If
    End If
Application.ScreenUpdating = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matt MaliAuthor Commented:
Hurray..its working.Thanks Neeraj
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Matt! Glad it worked as desired. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.