Auto create new worksheet when select option from drop down list

JANET JONG · #1 08-01-2018, 01:00 AM

hi alls,

i am having a problem in creating new worksheet automatically when i change my selection from drop down list.

To be clear, i have a list from January to December in my drop down list. I would like excel help me to create a worksheet with a name "January" when i select January from drop down list.

Thanks in advance. I am a beginner in excel. help to have a step by step explanation from you all.

NoSparks · #2 08-01-2018, 07:13 AM

Try this
Right click the sheet tab of the sheet with the validation drop down > select View Code > paste this into the sheet module that opens.
You'll need to adjust the drop down cell address to what you have. A2 is used here.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    
'limit to the specific cell
If Target.Address <> "$A$2" Then Exit Sub

'check if sheet already exists
On Error Resume Next    'suppress error message in case doesn't exist
Set ws = Sheets(Target.Value)
On Error GoTo 0         're-enable error messages

'if ws is nothing then sheet doesn't exist
If ws Is Nothing Then
    'add the sheet to end of tab lineup
    Worksheets.Add After:=Sheets(Sheets.Count)
    'the newly added sheet is now the active sheet, name it
    ActiveSheet.Name = Target.Value
    'go back to original sheet
    Me.Select
    'display message
    MsgBox "New sheet added for " & Target.Value
Else
    'the sheet alread exists, display message
    MsgBox "A sheet for " & Target.Value & " already exists."
End If

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word document with text depending on drop down list option	miguelcane	Word VBA	1	04-30-2018 06:51 PM
Show/ hide three sections of a cover page based on the option selected in the drop down list	raizadamili	Word	3	04-18-2018 06:56 AM
Select multiple items from a drop down list	ConfuddledOne	Excel Programming	0	01-30-2017 08:13 PM
Auto Text Drop Down List	AJohn403	Word	3	05-01-2015 05:00 AM
Help, auto select drop down by another drop down value?	skrallemand	Word VBA	3	09-11-2013 11:55 AM