VBA - Find & Replace using table in another workbook

[tsheffer]

I have a table saved in a workbook with two columns for find and replace which has multiple rows.
I found the following code (less the keyboard shortcut) and changed the file path to match mine.
This is saved in Personal.xlsb so it could work with any workbook that is opened.
When open a workbook that I want to use this code for, no changes are made.
I tried changing the Set Target = ActiveWorkbook
Any suggestions?



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

' Keyboard Shortcut: Ctrl+Shift+K

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("W:\Departmental Documents\Sales\Pricing\Price List Charges.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

 

[tsheffer]

I tried again and changing the Set Target = ActiveWorkbook enabled the code to Run in VBA but the shortcut key didn't work.
After doing more research online, found a post that said Ctrl+Shift+"key" does not work with Workbooks.Open. Shortcut needs to be Ctrl+"key".
Works great now!

 

[kadambari]

I want a vba macro to open multiple text files, replace the asterisk(*) character which is always at the start of the text and save/close the files. Also i want the code to provide me option of selecting multiple files when the selection box prompts.
can someone please help me with this code. I am very new to VBA

 

[Fluff]

Please start a thread of your own for this question. Thanks