Increment the highest existing number, looks good!

Regards,
Peter T

"Rick Rothstein (MVP - VB)" wrote in
message ...
Excellent point! Here is my revision...

Private Sub CommandButton2_Click()
Dim Sh As Worksheet
Dim MaxNewSheet As Long
Dim SheetName As String
Dim NewCustomerSheetName As String
MaxNewSheet = -1
For Each Sh In Sheets
SheetName = Sh.Name
If Left(SheetName, 12) = "New Customer" Then
If InStr(SheetName, ")") = 0 Then
SheetName = SheetName & " (0)"
End If
If Mid$(SheetName, 15, InStr(SheetName, ")") - 15) MaxNewSheet
Then
MaxNewSheet = Mid$(SheetName, 15, InStr(SheetName, ")") - 15)
End If
End If
Next
NewCustomerSheetName = "New Customer (" & CStr(MaxNewSheet + 1) & ")"
Sheets("Customer Number").Copy Befo=Sheets(3)
ActiveSheet.Name = Replace(NewCustomerSheetName, " (0)", "")
End Sub

Rick


"Peter T" <peter_t@discussions wrote in message
...
Ah, but what happens if user deletes an intermediate sheet, eg say
deletes
*3 but *4 still exists.
I think the only way is something along the lines of Jim's, ie loop
until
attempting to set a reference to Sheets(name & num) fails.

Regards,
Peter T


"Rick Rothstein (MVP - VB)" wrote
in
message ...
Give this a try...

Private Sub CommandButton2_Click()
Dim Sh As Worksheet
Dim Count As Long
Dim NewCustomerSheetName As String
For Each Sh In Sheets
If Left(Sh.Name, 12) = "New Customer" Then
Count = Count + 1
End If
Next
If Count = 0 Then
NewCustomerSheetName = "New Customer"
Else
NewCustomerSheetName = "New Customer (" & CStr(Count) & ")"
End If
Sheets("Customer Number").Copy Befo=Sheets(3)
ActiveSheet.Name = NewCustomerSheetName
End Sub

Rick


"Steve" wrote in message

...
Hello. I have a button that copies an existing worksheet and names
it
"New Csutomer". The problem is when the user pushes the button twice
the code errors out because there is already a sheet named "New
Customer". Is there a way to append a number to the new sheets so
this does not happen? Thanks!!

Private Sub CommandButton2_Click() 'Add new Customer Calc Sheets

Sheets("Customer Number").Copy Befo=Sheets(3)
ActiveSheet.Name = "New Customer"

End Sub