VBA input box help

[Leyton]

Hi,

Pretty sure this should be an easy one! I'm just starting out so need a little bit of a push in the right direction.

I want a bit of code that allows the user to enter in a value, then i want to use this value to name the newly created tab ( i already have this bit of code), i will also need to add some words after the value the user enters,

so for example if the user enters ONE in the input box, then i need the newly created tab to be called ONE PROMO,

i will probably need a warning on the amount of characters the user enters so it doesnt cause an error when naming the tab?

Any suggestions will be greatly appreciated!

Thanks

 

[Andrew Poulsom]

Example:

Sub Test()
    Dim Answer As Variant
    Answer = InputBox("Enter sheet name")
    If Len(Answer) > 0 Then
        Worksheets.Add.Name = Answer & " PROMO"
    End If
End Sub

 

[Leyton]

Hi Andrew,

Thanks for the reply, I'm trying to add your code into what i have existing but it is coming up with an error saying - Run time error 424, Object required, do you have any suggestions? I have copied in below what i have done -

Sub NewPromo()
'
' NewPromo Macro
' Adds in new tabs to create a new promo
'
'
Application.ScreenUpdating = False
Dim Answer As Variant
Answer = InputBox("Title of New Promotion", "New Tab Name")
Sheets(Array("Promo", "P&L")).Select
Sheets(Array("Promo", "P&L")).Copy Before:=Sheets(4)
If Len(Answer) > 0 Then
Sheets("Promo (2)").Select
Add.Name = Answer & " PROMO"
Sheets("P&L (2)").Select
Add.Name = Answer & " PROMO"
End If

 

[Leyton]

Sorry, just added in Worksheets. in front of the add name and its working however now its just creating the new tabs called what i want but no data and the data is going on the old tabs called Promo (2) & P&L (2)

 

[Andrew Poulsom]

What do you want the P&L sheet to be named?

 

[Leyton]

The same answer as the user gave in the input box, so if they enetered ONE in the input box we would have 2 tabs called ONE Promo and ONE P&L

 

[nightcrawler23]

I think you just have to rename the copied sheets.
If so, try the below code.

Sub NewPromo()
'
' NewPromo Macro
' Adds in new tabs to create a new promo
'
'
Application.ScreenUpdating = False
Dim Answer As Variant
Answer = InputBox("Title of New Promotion", "New Tab Name")

Sheets(Array("Promo", "P&L")).Select
Sheets(Array("Promo", "P&L")).Copy Before:=Sheets(4)

If Len(Answer) > 0 Then
    Sheets("Promo (2)").Name = Answer & " PROMO"
    Sheets("P&L (2)").Name = Answer & " P&L"
End If

End Sub

 

[Andrew Poulsom]

Try:

Sub NewPromo()
'
' NewPromo Macro
' Adds in new tabs to create a new promo
'
'
    Application.ScreenUpdating = False
    Dim Answer As Variant
    Answer = InputBox("Title of New Promotion", "New Tab Name")
    If Len(Answer) > 0 Then
        Sheets("Promo").Copy Before:=Sheets(4)
        ActiveSheet.Name = Answer & " PROMO"
        Sheets("P&L").Copy Before:=Sheets(5)
        ActiveSheet.Name = Answer & " P&L"
    End If
End Sub

 

[nightcrawler23]

Removed

 

[Leyton]

Thanks both of you, both worked perfectly!