Create new worksheet and use a number as its name..
 

I have a workbook that uses the following macro to create new
worksheets. What i would like to be able do is to create the sheet,
and have it automatically named [ in this case the name would a
number [2 or 3 or 4 or 5 etc etc]. The last sheet in the workbook
[ the rightmost tab] would be sheet '1' at the start. The newly
created sheet would be to the right if this, and I would want it to be
sheet '2'. Then the next sheet, created to the right of sheet '2',
would be named '3', and so on. How do I modify or change this VBA to
accomplish this?

Thanks,

Tonso

Dim sName As String
Dim wks As Worksheet
Answer = MsgBox("Do you want create a new MOST Sub-Operation?",
vbYesNo)
If Answer < vbYes Then Exit Sub
Worksheets("T").Copy After:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName < wks.Name
sName = Application.InputBox _
(Prompt:="Enter new worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
Set wks = Nothing

Create new worksheet and use a number as its name..
 

On Oct 8, 10:11*am, Billy wrote:
I have a workbook that uses the following macro to create new
worksheets. What i would like to *be able do is to create the sheet,
and have it automatically named [ in this case the name would *a
number [2 or 3 or 4 or 5 etc etc]. The last sheet in the workbook
[ the rightmost tab] would be sheet '1' at the start. The newly
created sheet would be to the right if this, and I would want it to be
sheet '2'. Then the next sheet, created to the right of sheet '2',
would be named '3', and so on. How do I modify or change this VBA to
accomplish this?

Thanks,

Tonso

Dim sName As String
* * Dim wks As Worksheet
* * Answer = MsgBox("Do you want create a new MOST Sub-Operation?",
vbYesNo)
* * If Answer < vbYes Then Exit Sub
* * Worksheets("T").Copy After:=Sheets(Worksheets.Count)
* * Set wks = ActiveSheet
* * Do While sName < wks.Name
* * * * sName = Application.InputBox _
* * * * * (Prompt:="Enter new worksheet name")
* * * * On Error Resume Next
* * * * wks.Name = sName
* * * * On Error GoTo 0
* * Loop
* * Set wks = Nothing

Sub copysheetandnamenextindex()
Sheets("Sheet5").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Sh" & Sheets(Sheets.Count).Index
End Sub