Sequential Sheets Issue

[asjmoron]

Hi Guys.

I have workbook that has numbered sheets in the format 001|002|003 etc...

I am trying write some VBA to duplicate the last sheet, formulas included, and continue the numbering of the sheets in the same format.

For example, the user has 30 sheets (001 - 030) and adds a new sheet. the code will duplicate 030 and create a new sheet named 031 with all forumlas included.

So far I have managed to do the duplication but I can not get the naming of the sheet down!

Any help would be great!

Below is the current code I have...…..

Sub Create()
    Dim I As Long
    Dim xNumber As Integer
    Dim xName As String
    Dim xActiveSheet As Worksheet
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xActiveSheet = ActiveSheet
    xNumber = InputBox("How Many More sheets do you need?")
    For I = 1 To xNumber
        xName = ActiveSheet.Name
        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
        ActiveSheet.Name = ActiveSheet.Name & I
    Next
    xActiveSheet.Activate
    Application.ScreenUpdating = True
End Sub

 

[JazzSP8]

Could you get the name of the last sheet and then convert it to a number so you can add 1 to the value?

Worksheets(Worksheets.Count).Select
NewSheetname = Val(ActiveSheet.Name) + 1

Can you work with that?

 

[MARK858]

Probably a better way but...

Sub Create()
    Dim I As Long
    Dim xNumber As Integer
    Dim xName As String
    Dim xActiveSheet As Worksheet
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xActiveSheet = ActiveSheet
    xNumber = InputBox("How Many More sheets do you need?")
    I = 1
    Do Until I = xNumber + 1
        xName = ActiveSheet.Name
        xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
        ActiveSheet.Name = Format(CLng(xActiveSheet.Name) + I, "000")
    I = I + 1
    Loop
    xActiveSheet.Activate
    Application.ScreenUpdating = True
End Sub

 

[footoo]

Replace this :

ActiveSheet.Name = ActiveSheet.Name & I

with this:

ActiveSheet.Name = Format(xName + 1, "000")

 

[asjmoron]

as always... you guys are lifesavers!