Search and Replace ALL

[sfran]

Hello,

I am trying to perform a simple find and replace in all cells across all sheets, including sheet names. I have code that works either to find and replace all across cells/sheets, but not the sheet name... OR code that only changes the sheet name but not the sheet contents... I need something that changes both what is in the sheets as well as the sheet names. I have tried combing portions of the code, but keep getting errors.

I want the option to put in the find/replace values via input box. The code I have is...


Works for all except for Sheet names...

If Range("A3") <> "<< Feature to Rename >>" Then
fnd = Worksheets("Project").Cells(3, 1)
rplc = Worksheets("Project").Cells(4, 2)

For Each WS In ActiveWorkbook.Worksheets
WS.Cells.Replace what:=fnd, replacement:=rplc, lookat:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next WS



Works for only Sheet names...

Dim xNum As Long
Dim xRepName As String
Dim xNewName As String
Dim xSheetName As String
Dim xSheet As Worksheet

xRepName = Application.InputBox("Please type in the word you will replace:", "Find Value", , , , , , 2)
xNewName = Application.InputBox("Please type in the word you will replace with:", "Replace With", , , , , , 2)

If xRepName = "false" Or xNewName = "false" Then Exit Sub
On Error GoTo ExitLab
For Each xSheet In ActiveWorkbook.Sheets
xSheetName = xSheet.Name
xNum = InStr(1, xSheetName, xRepName)
If xNum > 0 Then
xSheet.Name = Replace(xSheetName, xRepName, xNewName)
End If
ExitLab:
Next


I appreciate any guidance.

 

[Fluff]

How about

Sub sfran()
   Dim Ws As Worksheet
   Dim OldTxt As String, NewTxt As String
   
   OldTxt = InputBox("Please type the word to replace")
   If OldTxt = "" Then Exit Sub
   NewTxt = InputBox("Please enter the new word")
   If NewTxt = "" Then Exit Sub
   
   For Each Ws In Worksheets
      If InStr(1, Ws.Name, OldTxt, vbTextCompare) > 0 Then
         Ws.Name = Replace(Ws.Name, OldTxt, NewTxt)
      End If
      Ws.Range.Replace OldTxt, NewTxt, xlWhole, , False, , False, False
   Next Ws
End Sub

 

[sfran]

Thank you for the response... I tried copy & pasting the code.. getting:

Compile error: Argument not optional..

seems to be stopping after the End If at:
Ws.Range.Replace ...

Thanks

 

[Fluff]

Oops, forgot to change that, it should be Cells not Range

 

[sfran]

Works as advertised!!

Thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[hennLow]

You're welcome & thanks for the feedback.

Hi may I ask a question as well? How about setting an array for the find list and if it don't have a match the user will input the preferred data for it via input box.

 

[Fluff]

You have already started a thread for this, so you need to keep to that thread.

 

[hennLow]

oh ok thank you