renaming
 

I am trying to name the current sheet to the value of the
activecell. The code below is what I am using and it adds
an additional number rather than renumbering the sheet
that I am naming if the sheet already exists. Such as
Name1 the next would be Name12 the next would be Name123
then Name1234, how can I fix the code to a form like Name1
then Name2 then Name3 -- Similar to the way Excel does
this with sheets.

Thank You



strNewName = ActiveCell.Value

i = 0
newsh:
i = (i + 1)
strNewName = strNewName & i
For Each wks In ThisWorkbook.Worksheets
If wks.Name = strNewName Then
GoTo newsh:
ActiveSheet.Name = strNewName
blnDuplicate = True
End If
Next wks
If blnDuplicate = False Then
ActiveSheet.Name = strNewName
End If

renaming
 

hi,
your sheet is name1. in the loop you have strNewName =
name1 & i. I think that is your problem
you will have to truncate the Name1 to Name.
look up the trunc function and the trim function in vb help
regards
Frank
-----Original Message-----
I am trying to name the current sheet to the value of the
activecell. The code below is what I am using and it adds
an additional number rather than renumbering the sheet
that I am naming if the sheet already exists. Such as
Name1 the next would be Name12 the next would be Name123
then Name1234, how can I fix the code to a form like
Name1
then Name2 then Name3 -- Similar to the way Excel does
this with sheets.

Thank You



strNewName = ActiveCell.Value

i = 0
newsh:
i = (i + 1)
strNewName = strNewName & i
For Each wks In ThisWorkbook.Worksheets
If wks.Name = strNewName Then
GoTo newsh:
ActiveSheet.Name = strNewName
blnDuplicate = True
End If
Next wks
If blnDuplicate = False Then
ActiveSheet.Name = strNewName
End If
.