Renaming newly added worksheets

[Steve]

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 Before:=Sheets(3)
ActiveSheet.Name = "New Customer"

End Sub

 

[Jim Thomlinson]

Try something like this...

Sheets("Customer Number").Copy Before:=Sheets(3)
ActiveSheet.Name = SheetName("New Customer")

'**** Add this function
Public Function SheetName(ByVal strName As String) As String
Dim lng As Long
Dim wks As Worksheet

On Error Resume Next
Set wks = Sheets(strName)
Do While Not wks Is Nothing
lng = lng + 1
Set wks = Nothing
Set wks = Sheets(strName & lng)
Loop
SheetName = strName & lng
End Function

 

[Rick Rothstein \(MVP - VB\)]

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 Before:=Sheets(3)
ActiveSheet.Name = NewCustomerSheetName
End Sub

Rick

 

[Steve]

Hi Jim. I added the function, but got the same result. I put the
function in Module1. Am I doing something wrong?

 

[Steve]

Thanks Rick. That did the trick! Much appreciated.

 

[Peter T]

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\)]

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 Before:=Sheets(3)
ActiveSheet.Name = Replace(NewCustomerSheetName, " (0)", "")
End Sub

Rick

 

[Peter T]

Increment the highest existing number, looks good!

Regards,
Peter T