Tab sheet name
 

I have this code,
Private Sub Worksheet_Calculate()
Me.Name = Range("b36").Value
End Sub
that name the sheet tab based on cell B36. The problem is when I copy the
sheet and I enter the same name in the duplicate sheet and the previous sheet
I get an error. Is there a way to add a nuber at the end of the duplicate
sheet.
example:
1st sheet name = Round
2nd sheet name = square
3rd sheet name = round(2)
all sheet have the same calculations.

Tab sheet name
 

Try
'-----------------
Private Sub Worksheet_Calculate()
Dim strName
Dim j
j = 0
strName = Range("b36").Value
For Each ws In Worksheets
If (strName = ws.Name) Then
j = 2
End If
Next ws
If j 0 Then
strName = strName & "(" & j & ")"
End If
Me.Name = strName
End Sub
'-----------------

This will name the sheet as round(2) if round already exists
but will fail if round(2) exists.
You need to expand it to test for this condition, take the highest number
and add one to it...
"Han" wrote:

I have this code,
Private Sub Worksheet_Calculate()
Me.Name = Range("b36").Value
End Sub
that name the sheet tab based on cell B36. The problem is when I copy the
sheet and I enter the same name in the duplicate sheet and the previous sheet
I get an error. Is there a way to add a nuber at the end of the duplicate
sheet.
example:
1st sheet name = Round
2nd sheet name = square
3rd sheet name = round(2)
all sheet have the same calculations.

Tab sheet name
 

Code kinda works. Sometimes I copy the 1st sheet several times and give it
the same name because the same calcs are performed but for different
scenerios. I don't know if i'm being clear enough.
Here's another example that might clear things up.
1st sheet calcs volumn of a cylinder (sheet name = cylinder)
2nd sheet calcs volumn of cylinder of different height and radius (sheet
name = cylinder (2)
3rd sheet calcs volumn of cylinder of yet different height and radius (sheet
name = cylinder (3)

"Sheeloo" wrote:

Try
'-----------------
Private Sub Worksheet_Calculate()
Dim strName
Dim j
j = 0
strName = Range("b36").Value
For Each ws In Worksheets
If (strName = ws.Name) Then
j = 2
End If
Next ws
If j 0 Then
strName = strName & "(" & j & ")"
End If
Me.Name = strName
End Sub
'-----------------

This will name the sheet as round(2) if round already exists
but will fail if round(2) exists.
You need to expand it to test for this condition, take the highest number
and add one to it...
"Han" wrote:

I have this code,
Private Sub Worksheet_Calculate()
Me.Name = Range("b36").Value
End Sub
that name the sheet tab based on cell B36. The problem is when I copy the
sheet and I enter the same name in the duplicate sheet and the previous sheet
I get an error. Is there a way to add a nuber at the end of the duplicate
sheet.
example:
1st sheet name = Round
2nd sheet name = square
3rd sheet name = round(2)
all sheet have the same calculations.

Tab sheet name
 

Your requirement was clear... that is why I said "You need to expand it to
test for this condition, take the highest number
and add one to it... "

"Han" wrote:

Code kinda works. Sometimes I copy the 1st sheet several times and give it
the same name because the same calcs are performed but for different
scenerios. I don't know if i'm being clear enough.
Here's another example that might clear things up.
1st sheet calcs volumn of a cylinder (sheet name = cylinder)
2nd sheet calcs volumn of cylinder of different height and radius (sheet
name = cylinder (2)
3rd sheet calcs volumn of cylinder of yet different height and radius (sheet
name = cylinder (3)

"Sheeloo" wrote:

Try
'-----------------
Private Sub Worksheet_Calculate()
Dim strName
Dim j
j = 0
strName = Range("b36").Value
For Each ws In Worksheets
If (strName = ws.Name) Then
j = 2
End If
Next ws
If j 0 Then
strName = strName & "(" & j & ")"
End If
Me.Name = strName
End Sub
'-----------------

This will name the sheet as round(2) if round already exists
but will fail if round(2) exists.
You need to expand it to test for this condition, take the highest number
and add one to it...
"Han" wrote:

I have this code,
Private Sub Worksheet_Calculate()
Me.Name = Range("b36").Value
End Sub
that name the sheet tab based on cell B36. The problem is when I copy the
sheet and I enter the same name in the duplicate sheet and the previous sheet
I get an error. Is there a way to add a nuber at the end of the duplicate
sheet.
example:
1st sheet name = Round
2nd sheet name = square
3rd sheet name = round(2)
all sheet have the same calculations.