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

[Billy]

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

 

[Don Guillett Excel MVP]

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