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

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

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/

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

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

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)

"wennerberg " wrote in message
...
Thans for the help on this! You can imagine what it was like to keep
re-numbering my 30 worksheets as I re-arrenged them or inserted new
ones!


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