#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
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 |