Multi Find and Replace VBA Script Macro Not Working

[Paul_Hossler]

Well, since you asked

I'd turn it around a little

In the WB#1 with the lists, I'd have the macro below. It uses a list in the WB#1 with the From-To pairs

It opens a second WB#2 and replaces From-To pairs in WB#1 on all sheets in that WB#2

If then saves and closes WB#2

Seems to me musch easier than having the macro in many WBs and the From-To pairs in a 'database' WB

Option Explicit

'ORIGINAL SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
'Adapted for external data

Sub Multi_FindReplace()
 'PURPOSE: Find & Replace a list of text/values throughout entire workbook
    Dim wbReplaceList As Workbook
    Dim wbReplaceIn As Workbook
    Dim ws As Worksheet
    Dim sReplaceIn As String
    Dim rReplaceList As Range
    Dim iReplace As Long
    
    
    'get WB name to replace in
    sReplaceIn = Application.GetOpenFilename("File to Replace In, *.xls?", 1, "Select File To Replace In")
    If sReplaceIn = "False" Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Set wbReplaceIn = Workbooks.Open(sReplaceIn)
    Set wbReplaceList = ThisWorkbook
    Set rReplaceList = wbReplaceList.Worksheets(1).Cells(1, 1).CurrentRegion
    For Each ws In wbReplaceIn.Worksheets
        With rReplaceList
            For iReplace = 2 To .Rows.Count
                Call ws.UsedRange.Replace(.Cells(iReplace, 1).Value, .Cells(iReplace, 2).Value, xlWhole, , False)
            Next iReplace
        End With
    Next
    
    wbReplaceIn.Save
    wbReplaceIn.Close
    
    
    Application.ScreenUpdating = False
End Sub

MultiFind.xlsm has the macro and the From-To list, and ReplaceInHere.xlsx was my test WB to process