create a new tab automatically

[sbrown64]

HiI have a list and I have just added a new name to the list. I now go to my main entry sheet and when I go to column F and click the down arrow it appears, so far so good.My question is, is there a way of automatically creating a new tab at the bottom with the new name?RegardsStephen​

 

[sbrown64]

I copied and pasted the code above.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("F2:F100")) Is Nothing Then
If Not Evaluate("isref('" & Target.Value & "'!a1)") Then
Application.ScreenUpdating = False
Sheets.Add(, Sheets(Sheets.Count)).Name = Target.Value
Me.Activate
End If
End If
End Sub

I added this to give it a name
​

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub CREATE_NEW_TAB()[/FONT]

 

[Fluff]

Don't add anything to it, just copy the code I supplied & paste into the entry sheets code module.
It will run automatically whenever a cell in the range F2:F100 is changed.

 

[sbrown64]

when i paste your code and then click macros, it is not listed, no name is showing.

 

[Fluff]

It's not meant to be listed, it's what's called event code & will run automatically.

 

[sbrown64]

so i enter the code on the vba page for entry sheets? if i then go to the line and add a new name it will automatically work?

just tried it and nothing happened

 

[Fluff]

How are you entering the names in column F of that sheet?

 

[sbrown64]

I created a new name and added it to my lists tab, clicked sort and refresh, went to entry sheet and when I got to column F, I clicked the drop down arrow and selected the new name and then tabbed across to create a new line underneath.

 

[sbrown64]

just realised, your code is F2:F100, i am on line 493, so i added a zero to make it 1000 and now it works.

Thank you

 

[Fluff]

Glad you sorted it & thanks for the feedback