copy and create new sheet Allow duplicate worksheets but number them sequentially.

Hi
I need some help with the following code which does everything as above
but instead of adding
1,2,3 etc at the end of the duplicate name
goes
(0),(0) (1),(0)(1)(2) etc

Public Function checksheet(sheetName As String) As Boolean
Dim found As Boolean
Dim i As Integer
found = False
For i = 1 To Sheets.Count
If Sheets(i).Name = sheetName Then
found = True
End If
Next i
checksheet = found
End Function


Sub Button1_Click()
Sheets("NEW ACC").Select
Sheets("NEW ACC").Copy Before:=Sheets(3)


Dim nameOfSheet As String
'Dim newNameOfSheet As String
Dim haveName As Boolean
Dim extension As Integer

haveName = False
Do While (Not haveName)
nameOfSheet = InputBox("Enter a sheet name")
If checksheet(nameOfSheet) Then
extension = 0
If MsgBox("That name is already in use. Do you want a duplicate name?", vbYesNo) Then
Do While (Not haveName)
nameOfSheet = nameOfSheet & " (" & extension & ")"
If checksheet(nameOfSheet) Then
extension = extension + 1
Else
haveName = True
End If
Loop
End If
Else
haveName = True
End If
Loop

Range("C2").Select
ActiveCell.FormulaR1C1 = nameOfSheet

Sheets("NEW ACC (2)").Select
ActiveSheet.Name = [C2]
Range("C3").Select

Please note I have had help from other forums to get so far.

Any help will be appreciated.

Please Login or Register  to view this content.

Thanks for your reply
I an getting the same result by using both codes above.
I am trying so that my sheet names are followed by a single number
eg
Sales 1
Sales 2
Sales 3

not
Sales 1
Sales 1,2
Sales 1,2,3

Any ideas

Thank you

I read the result you wanted backwards. Try this...

Please Login or Register  to view this content.

Disregard the code in #4

Please Login or Register  to view this content.

Thank you
this works perfectly

AlphaFrog

Sorry to trouble you once again
The original code was part of a larger macro the code of which is as below.
I thought i would get help on part of it so split the macro and by trying
to integrate your code with mine would solve it but got completely lost.

I have three worksheets called
1) NEW ACC
2) MENU
3) Arrears Report

and a button on the menu page called create account with the below macro attached.

The problem goes (0), (0)(1), (0)(1)(2) if there are duplicates

Please Login or Register  to view this content.

Thank you in advance