Paste a value from a drop-down list-CodePudding

I am creating a macro that creates a new sheet inside my excel file, and I need that the sheet has the same same as the chosen place from the drop-down box.

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 need to replace "newname" with the health autority selected from the drop-down box.

As you can see the name is already stored, as I copied it. I was thinking that the solution may be using a paste spacial-value, but i don't know how to write it.

CodePudding user response：

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