Worksheet Gives Error on New Record (Paste)

[theteerex]

I have a code on one of my worksheets that automatically deletes records when called upon to do.
Unfortunately, when I create a new record on that sheet i.e. paste, I get the following error:
Run-time error '13':
Type mismatch

I only get this error when I try to paste values to multiple cells in the table.
Here is the problem, it only gives the error when I try to paste multiple values, when I paste to a single cell, there is no problem
Once I debug, excel returns that the problem is with the line colored in red below.

This is a problem as we have many rows of data and if the user cant' just copy and paste from source, it would be too cumbersome a tool.
And just to clarify, I am pasting into an existing table, simply overwriting existing data.

Public Sub Worksheet_Change(ByVal Target As Range) [COLOR=#008000]'_Change  creates the variable as a target, transferred as a value to "ProjectName"[/COLOR]
    Dim ans As Integer




[B][COLOR=#ff0000]    If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then[/COLOR][/B]


        ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
            If ans = vbYes Then
        
                With Application
                    .EnableEvents = False ' freeze WorkSheetChange event since you're about to UNDO a change
                    .Undo ' undo the change
                    ProjectName = Target.Value ' assign the deleted-undone value in to YYY
                    Rows(ActiveCell.Row).EntireRow.Delete ' delete that row
                    .EnableEvents = True ' re-enable all events
                End With
                MsgBox ProjectName & " has been deleted."  ' now you have that value in ProjectName
                        
            End If
        
    End If
DeleteRows (ProjectName) 'Gives sub-routine "DeleteRows" the company name
End Sub

 

[theteerex]

Does this need more explanation for help?
In my macro that calls the code above, I have the code below.
Is it monitoring only column B like I think it is?

Private Sub Worksheet_Change(ByVal Target As Range)[COLOR=#008000] '_Change  creates the variable as a target, transferred [/COLOR]as a value to "ProjectName"
    Dim ans As Integer
    
    If Target.Column = 2 And Target.Row > 1 And Target.Value = "" Then

        ans = MsgBox("Are you sure you want to Delete......This cannot Be Undone !!!", vbYesNo)
            If ans = vbYes Then
        
                With Application
                    .EnableEvents = False ' freeze WorkSheetChange event since you're about to UNDO a change
                    .Undo ' undo the change
                    ProjectName = Target.Value ' assign the deleted-undone value in to YYY
                    Rows(ActiveCell.Row).EntireRow.Delete ' delete that row
                    .EnableEvents = True ' re-enable all events
                End With
                MsgBox ProjectName & " has been deleted."  [COLOR=#008000]' now you have that value in ProjectName[/COLOR]
                        
            End If
        
    End If
DeleteRows (ProjectName)[COLOR=#008000] 'Gives sub-routine "DeleteRows" the company name[/COLOR]
End Sub

 

[Fluff]

Answered here https://www.mrexcel.com/forum/excel-questions/1049024-defining-active-cell-location.html#post5036644


Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.