Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-01-2018, 01:00 AM
JANET JONG JANET JONG is offline Auto create new worksheet when select option from drop down list Windows 10 Auto create new worksheet when select option from drop down list Office 2003
Novice
Auto create new worksheet when select option from drop down list
 
Join Date: Aug 2018
Posts: 1
JANET JONG is on a distinguished road
Default Auto create new worksheet when select option from drop down list

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.
Reply With Quote
  #2  
Old 08-01-2018, 07:13 AM
NoSparks NoSparks is offline Auto create new worksheet when select option from drop down list Windows 7 64bit Auto create new worksheet when select option from drop down list Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto create new worksheet when select option from drop down list 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:59 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft