Paste a value from a drop-down list

Question

I need the sheet to match the location selected from the drop-down box when I construct a macro that creates a new sheet inside of my excel file.

When you click Button 1, a new sheet appears but I don't know how to name is the same as the selected name. This is my code so far:

I must substitute the health authority I chose from the drop-down box for "newname."

The name is already stored, as you can see because I copied it. I was considering utilizing a paste spatial value as a potential solution, but I'm not sure how to do it.

Answer 1

Check if there is an existing sheet with the new name before adding to avoid the subsequent error.

Option Explicit

Sub createsheet()

   Dim sName As String, ws As Worksheet
   sName = Sheets("user").Range("M42").Value
   
   ' check if already exists
   On Error Resume Next
   Set ws = Sheets(sName)
   On Error GoTo 0
   
   If ws Is Nothing Then
       ' ok add
       Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
       ws.Name = sName
       MsgBox "Sheet created : " & ws.Name, vbInformation
   Else
       ' exists
       MsgBox "Sheet '" & sName & "' already exists", vbCritical, "Error"
   End If
   
End Sub