Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RangePass As Boolean
  If Target.Row > 4 And Target.Row < 51 Then
    If Target.Column = 4 And Target.Value <> "" Then CreateNewWorksheet (Target.Value)
  End If
  
End Sub
Sub CreateNewWorksheet(UseValue As String)
  Dim wb As Workbook, nws As Worksheet, NewName As String
  Dim ws As Worksheet, cws As Worksheet
  
  Set wb = ThisWorkbook
  Set cws = wb.ActiveSheet
  NewName = UseValue
  If wb.Worksheets.Count > 49 Then
    MsgBox "This workbook can only contain 50 worksheets."
    'If you dont want the messagebox to come up just comment it out with a '
    End
  End If
  For Each ws In wb.Worksheets
    If ws.Name = NewName Then
      MsgBox ("The name " & NewName & " is already in use. No new worksheet added.")
      'You can comment out the message box if you just want code to end.
      End
    End If
  Next ws

  'All passed add the new worksheet
  Set nws = wb.Worksheets.Add
  nws.Name = NewName
  cws.Activate  'Use a ' right before this line to select the new worksheet.
 
End Sub