Solved: Global Find and Replace

[K. Georgiadis]

I have 15 workbooks with multiple sheets and numerous occurrences of the company's name in each sheet. The problem is that recently the company adopted a new name; the old name has to be amended wherever it occurs.

I know how to do that by Edit>Find>Replace With but some of these workbooks contain as many as 60 worksheets making this a pretty tedious task. Instead of making the changes one by one, is there a way to do the following with code?:


unprotect all sheets (the sheets are protected with a ProtectAll routine)
run through every sheet, find and replace the old name with the new name wherever it occurs
reprotect all sheetsYour help is greatly appreciated

[Jacob Hilderbrand]

Try this macro:


Option Explicit

Sub ReplaceAll()

Dim ws As Worksheet
Dim OldText As String
Dim NewText As String
Dim Pass As String

'Text Being Sought
OldText = "String1"

'Replacement Text
NewText = "String2"

'Worksheet Password
Pass = "Password"

For Each ws In Worksheets
With ws
.Unprotect Password:=Pass
.Cells.Replace What:=OldText, Replacement:=NewText, MatchCase:=False
.Protect Password:=Pass, DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
Next

Set ws = Nothing

End Sub

[Jacob Hilderbrand]

Additionally you can loop through all the workbooks as well.

Assuming they are all in one folder and not in sub folders.

Option Explicit

Sub ReplaceAll()

Dim ws As Worksheet
Dim Wkb As Workbook
Dim OldText As String
Dim NewText As String
Dim Pass As String
Dim Path As String
Dim FileName As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

'Text Being Sought
OldText = "String1"

'Replacement Text
NewText = "String2"

'Worksheet Password
Pass = "Password"

'The Path For The Excel Files
Path = "C:\Test\"

FileName = Dir(Path & "*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & FileName)
For Each ws In Wkb.Worksheets
With ws
.Unprotect Password:=Pass
.Cells.Replace What:=OldText, Replacement:=NewText, MatchCase:=False
.Protect Password:=Pass, DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End With
Next
Wkb.Close SaveChanges:=True
FileName = Dir()
Loop

ExitSub:

Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

Set ws = Nothing
Set Wkb = Nothing

End Sub

[K. Georgiadis]

DRJ, if I understand this code correctly, it is designed to change all workbooks in a given directory. What if I simply wanted to add a stand-alone module to each workbook?

[K. Georgiadis]

:banghead: I created the macro in a separate workbook; I substituted the path of my files, entered the password, entered the new text and the old text. With all the target workbooks closed, I ran the macro from the separate workbook but nothing happened.

Then I tried to run the macro by opening the first workbook in the folder containing the target workbooks. When I ran the macro I got a runtime error 1004 "AutoFit method of range class failed."

What am I screwing up??:banghead:

[Jacob Hilderbrand]

Post your code. I don't have AutoFit in my code so it must be something else that is running.

[K. Georgiadis]

when I debugged for the runtime error I determined that the Autofit error was from a recorded macro that fills a blank worksheet with headings, years (2002-2006) then sets the columns to the necessary width. This is a macro that I can do without.

My code is identical to your code, except that I substituted for the real directory path and entered the real old and new company names. Perhaps I can try again by deleting the offending macro. Am I supposed to run this macro from a separate worksheet with all target worksheets closed?

Thanks for your patience.

[Jacob Hilderbrand]

Yea, run the macro from a separate workbook not in the folder you are searching and close all the other workbooks.

Make sure your path has a "\" at the end.

i.e.

C:\Test\ (Good)
C:\Test (Bad)

[K. Georgiadis]

:beerchug: The missing backslash was the culprit! I fixed it and the macro worked lika a dream...Thanks


PS: I used to be able to mark "solved" from the thread tools. Has it moved?