Renumber a WorkSheet

[Guest]

Is there a way to renumber an excel worksheet?

 

[Myles]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Mike Fogleman]

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