VBA to number worksheets?

[wennerberg]

I am striving to build a VBA macro that will numeber and re-number m
worksheets by putting numbers before the name of each worksheet. (
find this easiest for referring to them in training, documenataion, an
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 th
following:

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

This below works, but obviously adds another number every time it i
run. (Code may be recognized as being based someone else's code t
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

TI

 

[Bob Phillips]

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]

Thans for the help on this! You can imagine what it was like to kee
re-numbering my 30 worksheets as I re-arrenged them or inserted ne
ones

 

[Bob Phillips]

It's what computers are for.

--

HTH

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