VBA - Find & Replace using table in another workbook

tsheffer

New Member
Joined
Sep 16, 2013
Messages
14
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

tsheffer

New Member
Joined
Sep 16, 2013
Messages
14
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

New Member
Joined
Mar 18, 2021
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,231
Office Version
  1. 365
Platform
  1. Windows
Please start a thread of your own for this question. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,126,953
Messages
5,621,805
Members
415,857
Latest member
braunReivn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top