"Find and Replace" in Sheet names

  • Thread starter Thread starter Excel
  • Start date Start date
E

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