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

Try something like this...

Sheets("Customer Number").Copy Befo=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
--
HTH...

Jim Thomlinson


"Steve" wrote:

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

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

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

On Mar 25, 4:16*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Try something like this...

Sheets("Customer Number").Copy Befo=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
--
HTH...

Jim Thomlinson



"Steve" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Rick. That did the trick! Much appreciated.

On Mar 25, 4:31*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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- Hide quoted text -

- Show quoted text -

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

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

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