create a new tab automatically

sbrown64

Board Regular
Joined
Aug 23, 2019
Messages
87
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
How about
Code:
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
 
Upvote 0
That code needs to go into the sheet module where data validation dropdown is located.
 
Upvote 0
is that the main entry sheet or the lists tab?

I have tried it on both tabs and I get an error

object required.
 
Upvote 0
It's the sheet you select the value on, as per your op.
 
Upvote 0
As i said, i created a new name to a list on the lists tab, then i went to the entry sheet and chose the name in the cell, i pressed tab and then run macro and error came up.

object required.
 
Upvote 0
Did you put the code in the Entry Sheets code module?
If so what line of the code gave the error?
 
Upvote 0
when i click step into the code, it highlights this and when i press F8 again
The message run time error 424, object required pops up
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("F2:F100")) Is Nothing Then[/FONT]
 
Upvote 0
Have you changed the code at all? because you cannot step-into the code I supplied.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top