How can I automatically create a new sheet titled with a specific data input?

[Jbab]

Hi all!!

Here’s my question:
If I have a data table with names for example:
A1: Mark

A2: Brian

A3: Luke

…

A20: John
A21:


Now let’s say A21 is blank. Is there a way for excel to automatically create a new sheet (tab on the bottom) titled “Tony” if I typed “Tony” into cell A21?



Not sure if it would be a formula or something else. Anything helps!!

 

[My Aswer Is This]

If you enter Alpha into Range ("A2") you want to create a new sheet named Alpha
Is that what you want? Or any other range in column 1

 

[Jbab]

If you enter Alpha into Range ("A2") you want to create a new sheet named Alpha
Is that what you want? Or any other range in column 1

Sorry, I don’t follow.

I think I would just want a macro or a way to automatically create a new sheet with the title of A21 but ONLY when I input the data in that cell. Not sure if that makes sense?

 

[My Aswer Is This]

Sorry, I don’t follow.

I think I would just want a macro or a way to automatically create a new sheet with the title of A21 but ONLY when I input the data in that cell. Not sure if that makes sense?

OK Only "A21"

 

[My Aswer Is This]

Try this: The script runs when you enter any value in Range("A21")
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/3/2022  7:24:46 PM  EST
If Target.Address = "$A$21" Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Dim ans As String
Dim sn As String
sn = ActiveSheet.Name
ans = Target.Value
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
Application.Goto Sheets(sn).Range("A20")
End If
Exit Sub
M:
MsgBox ans & "   Is not a proper sheet name or sheet already exist"
ActiveSheet.Delete
Application.Goto Sheets(sn).Range("A20")
End Sub

 

[Jbab]

Try this: The script runs when you enter any value in Range("A21")
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  11/3/2022  7:24:46 PM  EST
If Target.Address = "$A$21" Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Dim ans As String
Dim sn As String
sn = ActiveSheet.Name
ans = Target.Value
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
Application.Goto Sheets(sn).Range("A20")
End If
Exit Sub
M:
MsgBox ans & "   Is not a proper sheet name or sheet already exist"
ActiveSheet.Delete
Application.Goto Sheets(sn).Range("A20")
End Sub

Thank you!!

 

[My Aswer Is This]

Thank you!!

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.