I am struggling with the code below - and frankly not sure if I am using the right code for my purpose. I have a file, named test.xlsm. The file has two columns, A and B. I want to run the script in other files of my choice, and hoping that will mean that any cells that have text from A, will have such text replaced with content from B. Am I doing this wrong? Thank you for your help.


Sub Multi_FindReplace()'PURPOSE: Find & Replace a list of text/values throughout entire workbook
'ORIGINAL SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
'Adapted for external data




Dim sht As Worksheet
Dim fndList As Variant
Dim x As Long
Dim Source As Workbook
Dim Target As Workbook




Set Target = ThisWorkbook
Set Source = Workbooks.Open("C:\Users\NAME\Desktop\test.xlsm")




fndList = Source.Sheets(1).Range("A:B").SpecialCells(2).Value
Source.Close False




'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
      For Each sht In Target.Worksheets
        sht.Cells.Replace What:=fndList(x, 1), Replacement:=fndList(x, 2), _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht
  Next x
End Sub