hello,
I'm using Validation list in my sheet, and in case I've got a new item not in the list, the below code helping me to just type it and it will automatically added to the validation list,
but i face 2 problems which is :
First: I want the new item to be added in a new row, not just a new cell in the column.
Second : I use 2 worksheets for validation lists which (List - Subs) and I want to effect only Subs to add a new row, "List" is working fine by adding a new cell only
here is the code
Option Explicit
'add name to a list if its not in the data validation's list

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim ws As Worksheet
Dim str As String
Dim i As Integer
Dim rngDV As Range
Dim rng As Range

If Target.Count > 1 Then Exit Sub
Set ws = Worksheets("list")
Set ws = Worksheets("subs")
  
If Target.Row > 1 Then
  On Error Resume Next
  Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  If rngDV Is Nothing Then Exit Sub
  
  If Intersect(Target, rngDV) Is Nothing Then Exit Sub
    
  str = Target.Validation.Formula1
  str = Right(str, Len(str) - 1)
  On Error Resume Next
  Set rng = ws.Range(str)
  On Error GoTo 0
  If rng Is Nothing Then Exit Sub
  
  If Application.WorksheetFunction _
    .CountIf(rng, Target.Value) Then
    Exit Sub
  Else
    i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
    ws.Cells(i, rng.Column).Value = Target.Value
    rng.Sort Key1:=ws.Cells(1, rng.Column), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
  End If

End If
thank you in advance