Paste a value from a drop-down list

0 votes

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.

enter image description here

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:

enter image description here

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.

Jan 14, 2023 in Others by Kithuzzz
• 38,000 points
559 views

1 answer to this question.

0 votes

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
answered Jan 14, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to create a dependent drop down list using [Apache POI]

There is nothing apache poi cannot do, ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
5,951 views
0 votes
1 answer

Create Drop-Down List Using a Formula (Without Data Validation)

As far as I am aware, it ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,600 points
3,015 views
0 votes
1 answer

Choosing a random text value from a list

Try this: =CHOOSEROWS(FILTER(B2:B100,A2:A100=FALSE),RANDBETWEEN(1, ...READ MORE

answered Apr 3, 2023 in Others by narikkadan
• 63,600 points
363 views
0 votes
0 answers

How to drop all tables from a database with one SQL query?

What if we don't want to type ...READ MORE

May 28, 2022 in Others by Sohail
• 3,040 points
391 views
0 votes
1 answer

Create dropdown list in excel that displays different values from the same lookup table

Looks like the Worksheet_Change() sub needs two ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,600 points
562 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,258 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,696 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
968 views
0 votes
1 answer

How to remove values from drop-down if value deleted from validation list

Try this code: Private Sub Worksheet_Change(ByVal Target As ...READ MORE

answered Dec 11, 2022 in Others by narikkadan
• 63,600 points
653 views
0 votes
1 answer

How to create a drop-down list in Excel?

Making a list of the items you ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,600 points
747 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP