Is there a way to renumber an excel worksheet?

To rename 5 WorkSheets, for example:

Sub RenameWorksheets()

shtName = Array("Apple", "Bean", "Cherry", "Diamond", "Earl")

For i = 1 To Sheets.Count 'assumes 5 worksheets total
Sheets(i).Name = shtName(i - 1)
Next

End Sub

Ht

--
Myle
-----------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874
View this thread: http://www.excelforum.com/showthread.php?threadid=49731

Yep, knew that one.. but i want to change the value "Sheet37" (Tools) to
"Sheet5" (Tools)... not "Sheet37" (Tools) to "Sheet37" (bunny)

:)

"Myles" wrote:


To rename 5 WorkSheets, for example:

Sub RenameWorksheets()

shtName = Array("Apple", "Bean", "Cherry", "Diamond", "Earl")

For i = 1 To Sheets.Count 'assumes 5 worksheets total
Sheets(i).Name = shtName(i - 1)
Next

End Sub

HtH


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=497319

Sub RenameSheets()
Dim aryOld
Dim aryNew
Dim sh As Worksheet
Dim i As Long

aryOld = Array("Sheet17", "Sheet21", "Sheet37")
aryNew = Array("Sheet1", "Sheet2", "Sheet5")

For Each sh In Worksheets
For i = LBound(aryOld) To UBound(aryOld)
If sh.CodeName = aryOld(i) Then
sh.Parent.VBProject.VBComponents(sh.CodeName) _
.Properties("_CodeName") = aryNew(i)
Exit For
End If
Next i
Next sh

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mirco Wilhelm" wrote in message
...
Yep, knew that one.. but i want to change the value "Sheet37" (Tools) to
"Sheet5" (Tools)... not "Sheet37" (Tools) to "Sheet37" (bunny)

:)

"Myles" wrote:


To rename 5 WorkSheets, for example:

Sub RenameWorksheets()

shtName = Array("Apple", "Bean", "Cherry", "Diamond", "Earl")

For i = 1 To Sheets.Count 'assumes 5 worksheets total
Sheets(i).Name = shtName(i - 1)
Next

End Sub

HtH


--
Myles
------------------------------------------------------------------------
Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
View this thread:
http://www.excelforum.com/showthread...hreadid=497319

Another way is to manually change the CodeName property in the VBE tree.
Select the sheet from the tree, then from the main menu, View/Properties
Window (or F4). This brings up the properties window of the worksheet.
Notice there are 2 places for Name. The one at the top, (Name) is the Code
Name. The one further down the list is the name that appears in parentheses
on the tree and on the sheet tab. Be careful of creating duplicates.

Mike F
"Mirco Wilhelm" wrote in message
...
Is there a way to renumber an excel worksheet?