Search and Replace ALL

sfran

New Member
Joined
Mar 9, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Mar 9, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Oops, forgot to change that, it should be Cells not Range
 
Solution

sfran

New Member
Joined
Mar 9, 2021
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
Works as advertised!! :)

Thank you!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,037
Messages
5,639,670
Members
417,104
Latest member
Nelsini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top