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.
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.