Microsoft Office Forums - View Single Post - Auto create new worksheet when select option from drop down list

NoSparks · 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