VBA - Copy sheet, rename based on user input

[joey255]

Hello,

Ive had a look through the forum and can't find the answer I'm looking for. I'm looking to automate copying sheets and re-naming them based on a user input.

I have an "Input" sheet that has a form control button that I currently use to copy a sheet called "Test". What I'd like to do is once I create new "Test (2)" it will add then name in based on user input sheet in cell B1, then "Test (3)" based on B2, "Test (4)" B3...etc. So every time I add a sheet it will be named automatically after the user input. If it was Apples, Bananas, Cocunut in B1 to B3, then my new sheets would be named that.

Hopefully that makes sense?

Many thanks for any help in advance.

 

[MrTomasz]

Show the code which you are using to copy sheet. It will be easier to add.

 

[joey255]

Its a little bit complicated as I'm using a userform - I've managed to get it to work but it is cumbersome as I use a few layered cells. It looks like it is working as the new sheets are created with the names put in the userform.

What I'm trying to do is 1) simplify it, 2) in the newly created sheet, create a cell that has the tab name in Cell A1.

Private Sub CommandButton1_Click()
Sheet1.Range("C9") = TextBox1.Value
Sheet1.Range("C10") = TextBox2.Value
Sheet1.Range("C11") = TextBox3.Value
Sheet1.Range("C12") = TextBox4.Value
Sheet1.Range("C13") = TextBox5.Value
Sheet1.Range("B9") = TextBox6.Value
Sheet1.Range("B10") = TextBox7.Value
Sheet1.Range("B11") = TextBox8.Value
Sheet1.Range("B12") = TextBox9.Value
Sheet1.Range("B13") = TextBox10.Value


If TextBox1.Text = "" Then

StackedPay.Hide
Else
    Dim ws As Worksheet
    Set wh = Worksheets(Sheet1.Name)
    Sheet2.Copy After:=Sheet2
    
    If wh.Range("B9").Value <> "" Then
    ActiveSheet.Name = "GCoS - " & wh.Range("B9").Value
    End If
    
If TextBox2.Text = "" Then

StackedPay.Hide
Else
    Sheet2.Copy After:=Sheet2
    
    If wh.Range("B10").Value <> "" Then
    ActiveSheet.Name = "GCoS - " & wh.Range("B10").Value
    End If
If TextBox3.Text = "" Then

StackedPay.Hide
Else
    Sheet2.Copy After:=Sheet2
    
    If wh.Range("B11").Value <> "" Then
    ActiveSheet.Name = "GCoS - " & wh.Range("B11").Value
    End If
If TextBox4.Text = "" Then
StackedPay.Hide
Else
    Sheet2.Copy After:=Sheet2
    
    If wh.Range("B12").Value <> "" Then
    ActiveSheet.Name = "GCoS - " & wh.Range("B12").Value
    End If
If TextBox5.Text = "" Then

StackedPay.Hide
Else
    Sheet2.Copy After:=Sheet2
    
    If wh.Range("B13").Value <> "" Then
    ActiveSheet.Name = "GCoS - " & wh.Range("B13").Value
    End If
End If

End If
End If
End If
End If
StackedPay.Hide
Sheet1.Activate
End Sub

 

[joey255]

Please ignore the previous, I've extracted it and simplified it (kind of).

Sub OpenWelcome()
    Dim ws As Worksheet
    Set wh = Worksheets(Sheet1.Name)

If Sheet1.Range("C11") = "No" Then
Else
Sheet2.Copy After:=Sheet2
    If wh.Range("B20").Value <> "" Then
    ActiveSheet.Name = "GCoS - " & wh.Range("B20").Value
    End If
End If

If Sheet1.Range("B19") = "" Then
Else
Sheet2.Copy After:=Sheet2
    If wh.Range("B19").Value <> "" Then
    ActiveSheet.Name = "GCoS - " & wh.Range("B19").Value
    End If
End If

If Sheet1.Range("B18") = "" Then
Else
Sheet2.Copy After:=Sheet2
    If wh.Range("B18").Value <> "" Then
    ActiveSheet.Name = "GCoS - " & wh.Range("B18").Value
    End If
End If

If Sheet1.Range("B17") = "" Then
Else
    Sheet2.Copy After:=Sheet2
    If wh.Range("B17").Value <> "" Then
    ActiveSheet.Name = "GCoS - " & wh.Range("B17").Value
    End If
End If
Sheet2.Name = "GCoS - " & Sheet1.Range("B16").Value
wh.Activate

End Sub

 

[MrTomasz]

I'm lost. At the begining you have mentioned that you would like to rename the sheet after it is created. Now you say that you want to save sheet name in specific cell?