Runtime error 1004

[Vincent88]

Hi Guys, Please help to review what should be corrected when I run a code to create a new worksheet, this error message pop up. However a new worksheet is created anywhere. See the screenshot of error msg and debug.

Private Sub Workbook_SheetBeforeRightClick(ByVal sh As Object, ByVal Target As Range, Cancel As Boolean)
      
   If sh.Name = "Data" Then Exit Sub
        
   If Target.CountLarge > 1 Then Exit Sub
  
   Application.DisplayAlerts = False
   Application.EnableEvents = False
   Application.ScreenUpdating = False
    
   'CREATE CONSECUTIVE MONTH WORKSHEET
    Select Case Target.Address
        Case "$A$1"
            Cancel = True
            AddNextMonth1
        
    'UPDATE THE LAST FIVE DAYS DATA
        Case "$A$2"
            Cancel = True
            FindLastCell_In_ColumnA4
            LastMonthData17
    End Select
    
'UPDATE USED NAMES AND CREATE DROPDOWN LIST TO COLUMN A
If Not Intersect(Target, Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row + 1)) Is Nothing Then 
 
  If Target.Offset(-1) <> "" Then
     Cancel = True
      
     Dim lrA As Long
     Dim colAarray As Object
     Dim clA As Range
     Dim SortedColA_array As Variant
  
    'Creating a array list
    Set colAarray = CreateObject("System.Collections.ArrayList")
    
    'Physical Source in Column L
    lrA = Range("A2").End(xlDown).Row
    For Each clA In Range("A3:A" & lrA)
          If Not colAarray.contains(clA.Value) Then colAarray.Add clA.Value
    Next clA
      
    colAarray.Sort
    SortedColA_array = colAarray.toarray
    
    'Output SortedColA_array to Sheet("Data") Column O
    Sheets("Data").Range("T2:T" & Sheets("Data").Range("T2").End(xlDown).Row).ClearContents
    Sheets("Data").Range("T2").Resize(UBound(SortedColA_array) + 1, 1).Value = Application.Transpose(SortedColA_array)

    End If

    'Compare Sheet("Data") Column N and Column O to find the Unused Names
     Const F = "TRANSPOSE(IF(ISNA(MATCH(S2:S#,T2:T¤,0)),S2:S#))"
 
        Dim V
        With Sheets("Data")
            V = .Cells(.Rows.Count, 21).End(xlUp).Row:  If V > 1 Then .Range("U2:U" & V).Clear
            V = Filter(.Evaluate(Replace(Replace(F, "#", .[S1].End(xlDown).Row), "¤", .[S1].End(xlDown).Row)), False, False)
            If UBound(V) > -1 Then .[U2].Resize(UBound(V) + 1).Value2 = Application.Transpose(V)
        End With
 
            Dim strSortedAgt As String
            Dim lr As Long
            
    'Create DropDown List to Column A
     lr = Sheets("Data").Range("U2").End(xlDown).Row
     strSortedAgt = "='Data'!$U$2:$U$" & lr
 
   With Target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=strSortedAgt     
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
   End With
  
   End If
  
   Application.DisplayAlerts = True
   Application.EnableEvents = True
   Application.ScreenUpdating = True
    
End Sub

 

[Zot]

Try to add sh if it works.
If Not Intersect(Target, Range("A3:A" & Range("A" & sh.Rows.Count).End(xlUp).Row + 1)) Is Nothing Then

 

[RoryA]

I'd add sh to all the Range calls too:

If Not Intersect(Target, sh.Range("A3:A" & sh.Range("A" & sh.Rows.Count).End(xlUp).Row + 1)) Is Nothing Then