Ok not too bad. This code will check if the name already exists and will check if there are over 49 worksheets. Right now it has message boxes that come up if one of these stops occur. You can simply place a ' in front of any line of code to comment it out and cause Excel to ignore it.
You will need to paste this entire code into the actual worksheet object in the visual basic editor. Place it in the main worksheet that you will use.
Let me know if you have any questions.
Thanks
Code:
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