Sanjida Ahmed
Sometimes we need to modify our dataset for some reason. And when we are dealing with multiple worksheets and workbooks then this is really hard to get the job done. But if you know the correct use of Excel’s Find and Replace tool and the VBA code then this hard task becomes quite easy. In this article, we will show you how to find and replace values in multiple sheets and files in Excel with the Find and Replace tool and VBA macro.
How to Find and Replace Values in Multiple Excel Files:3 Methods
In the upcoming sections, you will learn how to find and replace certain values in multiple Excel sheets and files with the Find and Replace feature and with the VBA macro in Excel.
1. Find and Replace Values in Multiple Worksheets in an Excel File
You can find a certain value from all the worksheets of the existing workbook and replace them all at once with any value that you want. Let’s show you how you can do that with Excel’s Find & Replace tool.
In the following image, you can see that we have an Excel sheet named One.
In the same workbook, we have another sheet named Two.
Now what we are going to do here is, we will find a specific word and replace it with another word with the Find & Replace feature in Excel. In our case, we will replace the word “Ambrose” with “Moxley”.
Steps:
- From the Home tab, select Find & Select -> Replace…
- Following that, a Find and Replace pop-up box will appear. Click on the Options>> (shown in the image below).
- After that, new options will turn up in the pop-up box. Write the word that you want to find (in our case, it is “Ambrose”) in the Find what section.
- Then, write the word that you want to replace the previous word with (the word is “Moxley” in our case) in the Replace with section.
- After that, from the drop-down list next to Within, select Workbook. This will find the given word in all the sheets in that workbook and replace all of them.
- Lastly, click Replace All.
You will get a confirmation box stating all the replacements that happened. As we have only 2 worksheets in our workbook so only 2 replacements occurred in our case. If you have more sheets, then more replacements will take place.
Now let’s check out whether the replacements really occurred or not!
Consider both the above and the following images.
The word “Ambrose” from both of our sheets is now replaced with the word “Moxley”.
2. Find and Replace Values in Multiple Sheets of Multiple Workbooks
Do you know that after you are done performing the Find & Replace operation for e certain worksheet, the Find and Replace pop-up box won’t get closed until you manually close it? So, you can take the advantage of this feature when you need to replace values in multiple workbooks.
Look at the following images. We have opened two Excel workbooks. One is named Find and Replace Value (see the image below).
Another one is named Transpose Multiple Columns (shown in the picture below).
Now, after you have done replacing value from one specific worksheet discussed in the above section, you will notice that the Find and Replace pop-up box is still there.
- From there, all you have to do is, instead of clicking the Close button, just hover over the next workbook and press Replace All. It will find the values that you want and replace them with the value that you provide from all the worksheets of that workbook as well.
You can perform this operation on as many workbooks as you require.
Similar Readings:
- How to Find and Replace Asterisk (*) Character in Excel
- Find and Replace a Text in a Range with Excel VBA
- How to Find and Replace in Excel Column
- How to Find and Replace within Selection in Excel
3. Embed VBA to Find and Replace Values in Open Workbook in Excel
You can do all of that discussed in the previous sections just with a VBA code. If you are an advanced Excel user, then this section is for you. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. You can find and replace values from all worksheets of a workbook in Excel. Let’s see how to do that with VBA macro.
Steps:
- In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.
- Next, in the pop-up code window, from the menu bar, click Insert -> Module.
- Then, copy the following code and paste it into the code window.
Sub MultipleFindAndReplace()
Dim iSheet As Worksheet
Dim OldValue As Variant
Dim NewValue As Variant
Dim i As Long
OldValue = Array("John", "Roman", "Dean", "Seth", "Finn")
NewValue = Array("Ben", "Alex", "Joe", "Chris", "Josh")
For i = LBound(OldValue) To UBound(OldValue)
For Each iSheet In ActiveWorkbook.Worksheets
iSheet.Cells.Replace What:=OldValue(i), Replacement:=NewValue(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next iSheet
Next i
End SubYour code is now ready to run.
This piece of code will replace the array of values “John, Roman, Dean, Seth, Finn” with the values “Ben, Alex, Joe, Chris, Josh”.
- Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.
After the code execution, look at the image below to see the result.
All the values (“John, Roman, Dean, Seth, Finn”) that you entered in your code to replace with the values (“Ben, Alex, Joe, Chris, Josh”) are now successfully replaced.
You can also execute this code to find and replace multiple values from multiple worksheets in multiple workbooks.
- After running the macro on one workbook, now just hover over to the other one. Then from there, go to Developer -> Macros.
- A Macro pop-up box will appear. There select the macro name and click Run.
You will see that the same result will be applied to the workbook that you opened later. The find and replace values that you entered in the code will successfully do their job in finding those words and replacing them.
VBA Code Explanation
Dim iSheet As Worksheet
Dim OldValue As Variant
Dim NewValue As Variant
Dim i As LongDefining the variables.
OldValue = Array("John", "Roman", "Dean", "Seth", "Finn")
NewValue = Array("Ben", "Alex", "Joe", "Chris", "Josh")Set the values to find in the OldValue variable and set the values to replace in the NewValue variable.
For i = LBound(OldValue) To UBound(OldValue)
For Each iSheet In ActiveWorkbook.Worksheets
iSheet.Cells.Replace What:=OldValue(i), Replacement:=NewValue(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next iSheet
Next iThis piece of code will first loop through each item in the Array lists. Then loop through each worksheet in ActiveWorkbook. After scanning, it will replace the OldValue with the NewValue when it finds the match. After the iteration in one sheet, it will then move into the next sheet and start executing the loop again.
Read More: Find And Replace Multiple Values in Excel
Download Workbook
You can download the free practice Excel workbook from here.
Conclusion
To conclude, this article showed you different methods on how to find and replace values in multiple sheets and files in Excel with the Find and Replace tool and VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.
Related Articles
- How to Find and Replace Multiple Values in Excel with VBA
- How to Find and Replace Using Formula in Excel
- How to Find and Replace Values Using Wildcards in Excel
- Find and Replace Tab Character in Excel
- How to Find and Replace Multiple Words from a List in Excel
- How to Find and Replace Text Color in Excel
- [Fixed!] Excel Find and Replace Not Working
- Excel VBA to Find and Replace Text in a column
- Excel VBA: How to Find and Replace Text in Word Document
- How to Find and Replace from List with Macro in Excel
