"Find and Replace" in Sheet names

[Excel]

Is there a way (with code perhaps) to search and replace sheet names?

For example, I have 20 sheet names with "Direct (2)" in the name, and
I'ld like to replace teh "Direct (2)" in the sheet name to "Net" in
each instance.

Thanks, Jim

 

[JE McGimpsey]

One way:

Public Sub Direct2ToNet()
Const sRepl As String = "Direct (2)"
Dim ws As Worksheet
Dim nPos
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
With ws
If .Name Like "*" & sRepl & "*" Then _
.Name = Replace(.Name, sRepl, "Net")
End With
Next ws
On Error GoTo 0
End Sub

 

[Brad Detchevery]

Example VBA Macro to play with.

Hope this helps

....

Sub Renamesheets()

Dim S As Worksheet

For Each S In ActiveWorkbook.Sheets
V = InStr(1, S.Name, "Direct", vbTextCompare)
If (V > 0) Then
S.Name = "Net " + Mid(S.Name, V + 7, Len(S.Name))

End If

Next
End Sub