"Find and Replace" in Sheet names

[[email protected]]

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




In article <[email protected]>,
"[email protected]" <[email protected]> wrote:

> 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

[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


[email protected] wrote:
> 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
>