VBA preventing me from deleting or highlighting rows and columns

Hi Everyone, I am using this formula (shown below) to copy all the data entered in a row from one sheet to another sheet, depending on cue words entered in a particular column. The problem I'm ha...

EXAMPLE VBA ERROR.xlsm82 KB

excel

Macros and VBA

HansVogelaar

Mar 17, 2021

Jarvo

If you select more than one cell (for example an entire row, Target.Value is not a single value, but an array of values, so the comparison UCase(Target) = "SAVE" is not valid.

Add the following line at the beginning of the procedure:

If Target.CountLarge > 1 Then Exit Sub

Since the rest of the code will only be executed if Target is a single cell, you can replace Cells(Target.Row, Target.Column) with Target.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    Application.EnableEvents = False
    If Target.Column = 4 And UCase(Target) = "SAVE" Then
        Target.EntireRow.Copy Destination:= _
            Sheets("Saved").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
    If Target.Column = 4 And UCase(Target) = "CLOSED" Then
        Target.EntireRow.Copy Destination:= _
            Sheets("Archive").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
    Application.EnableEvents = True
End Sub