Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Renaming worksheets | Excel Worksheet Functions | |||
Worksheet renaming | Excel Discussion (Misc queries) | |||
Renaming folders | Excel Programming | |||
Renaming | Excel Programming | |||
Renaming within VBA | Excel Programming |