VBA to number worksheets?

  • Thread starter Thread starter wennerberg
  • Start date Start date
W

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
 
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)
 
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
 
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)
 
Back
Top