Here try this

Dim iCtr As Long
Dim iPos As Long

For iCtr = 1 To Worksheets.Count
On Error Resume Next
With Worksheets(iCtr)
iPos = InStr(1, .Name, ".")
If iPos 0 Then
.Name = iCtr & "." & Right(.Name, Len(.Name) - iPos)
Else
.Name = iCtr & "." & .Name
End If
If Err.Number < 0 Then
MsgBox "Trouble with " & Worksheets(iCtr).Name
Err.Clear
End If
End With
Next iCtr


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"wennerberg " wrote in message
...
I am striving to build a VBA macro that will numeber and re-number my
worksheets by putting numbers before the name of each worksheet. (I
find this easiest for referring to them in training, documenataion, and
verbal instructions, e.g. "Go to worksheet '17.Q4 Sales')

Below is what I have so far, but I need to enhance it to accomplish the
following:

If there is a period found at the within the first 5 characters of the
worksheet name, delete numbers found to LEFT of that period and replace
with appropriate sequential number.

This below works, but obviously adds another number every time it is
run. (Code may be recognized as being based someone else's code to
re-order worksheets or rename.)

Sub NumberWorksheets()

Dim iCtr As Long

For iCtr = 1 To Worksheets.Count
On Error Resume Next
Worksheets(iCtr).Name = iCtr & Worksheets(iCtr).Name
If Err.Number < 0 Then
MsgBox "Trouble with " & Worksheets(iCtr).Name
Err.Clear
End If
Next iCtr

End Sub

TIA


---
Message posted from http://www.ExcelForum.com/