VBA: Duplicate a sheet and give it a name based on a standard value and a variant

[2_nisia]

Hey everyone,

I have the following code:


...

If IsEmpty(Sheets("Start").Cells(number, 7).Value) Then
s = InputBox("How would you like to call it?")
If s = "" Then Exit Sub 'Sub gets exited if no sheet name entered or "Cancel" clicked

ActiveWorkbook.Sheets(Tax(Choice)).Visible = True 'Makes hidden sheet visible
Sheets(Tax(Choice)).Copy , Sheets(Sheets.Count) 'Copies the sheet now visible sheet and inserts at the end of all sheets
ActiveWorkbook.Sheets(Tax(Choice)).Visible = False 'Makes sheet hidden again'



With ActiveSheet.Name = Sheets("Tax_" + s)
MsgBox "The tax sheet is now created"

End With

...

I want to keep that Tax_ name and add the choice of the user next to it. So if the user chooses to name it house, I want the duplicated sheet to be named Tax_house

I have found threads on how to name sheets based on a standard value, or based on an activesheet name, but none that show how to combine both.

Hope you understand my question and thanks in advance!

 

[NdNoviceHlp]

Hi *2_nisia and Welcome to the Board! I think you can just use this code to name the sheet. HTH. Dave

ActiveSheet.Name = "Tax_" & s

ps. Please use code tags

 

[2_nisia]

Thank you for the comment.

Unfortunately it didnt

 

[Fluff]

Hi & Welcome to MrExcel.
How about

If IsEmpty(Sheets("Start").Cells(Number, 7).Value) Then
   s = InputBox("How would you like to call it?")
   If s = "" Then Exit Sub 'Sub gets exited if no sheet name entered or "Cancel" clicked

   ActiveWorkbook.Sheets(Tax(Choice)).Visible = True 'Makes hidden sheet visible
   Sheets(Tax(Choice)).Copy , Sheets(Sheets.Count) 'Copies the sheet now visible sheet and inserts at the end of all sheets
   ActiveSheet.Name = "Tax_" & s
   
   ActiveWorkbook.Sheets(Tax(Choice)).Visible = False 'Makes sheet hidden again'
   MsgBox "The tax sheet is now created"
End If

 

[2_nisia]

Hi & Welcome to MrExcel.
How about

If IsEmpty(Sheets("Start").Cells(Number, 7).Value) Then
   s = InputBox("How would you like to call it?")
   If s = "" Then Exit Sub 'Sub gets exited if no sheet name entered or "Cancel" clicked

   ActiveWorkbook.Sheets(Tax(Choice)).Visible = True 'Makes hidden sheet visible
   Sheets(Tax(Choice)).Copy , Sheets(Sheets.Count) 'Copies the sheet now visible sheet and inserts at the end of all sheets
   ActiveSheet.Name = "Tax_" & s
  
   ActiveWorkbook.Sheets(Tax(Choice)).Visible = False 'Makes sheet hidden again'
   MsgBox "The tax sheet is now created"
End If

Thanks a lot! That did the trick!! :D

 

[Fluff]

Glad we could help & thanks for the feedback.