I hate that this doesn't allow for proper line spacing and makes the code less readable, so I've inserted comment markers to break it up (this is my first ever reply, so I know of no other way to do it).
Personally, I'd set aside a specific cell to enter the sheet name, then do it on a button event to have more control over what happens (what if they make a typo?)
My functions are public as I'd usually have 'utility' functions in a global module to keep the code tidy and made available to the whole project.
I like to control absolutely everything, so I didn't even know about using "Intersect", so I would've written it like this:
public const topRangeRow = 3
public const bottomRangeRow = 5
public const rangeColNum = 3 ' target.column doesn't work with letters
'
' determines if the sheet already exists - good error checking!
Public Function sheetExists(ByVal sheetName As String) As Boolean
On Error Resume Next
'
Dim findSheet As Worksheet
'
Set findSheet = Worksheets(sheetName)
'
sheetExists = Not (findSheet Is Nothing)
'
End Function
'
' combines the error checking into a single function that tells you if it has been successful
Public Function createSheet(ByVal sheetName As String) As Boolean
On Error Resume Next
'
Dim newSheet As Worksheet
'
If (Not sheetExists(sheetName)) Then
'
Set newSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
'
If (newSheet Is Nothing) Then
'
createSheet = False
'
Else
'
newSheet.Name = sheetName
'
createSheet = True
'
End If
'
Else
'
createSheet = True
'
End If
'
End Function
'
Private Sub Worksheet_Change(ByVal Target As Range)
'
' determine if the edited cell is within the range you want
'
if ((target.row >= topRangeRow) and (target.row <= bottomRangeRow) and (target.column = rangeColNum)) then
'
If (not createSheet(mySheet.cells(target.row,target.column))) Then MsgBox "Could not create sheet"
'
end if
'
End Sub