- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Always Search the Whole Workbook in Excel?
You might have used the "Search" or "Find and Replace" functions in Excel many times. It will only search for a current active worksheet, but have you ever tried to apply the search to the whole workbook? This feature can’t be used by default in Excel, but we can make it happen by using the VBA codes in Excel. This process can help you save a lot of time when you are working with multiple worksheets at once. This tutorial will help you understand how we can always search the whole workbook.
Always Search in Whole Workbook
Here we will insert a VBA module and then run the code to complete the task. Let us look at a simple example to see how we can always search in the entire workbook using VBA codes.
Let us assume we have an Excel workbook that contains multiple worksheets similar to the images shown below.
Now to search in the whole workbook, right-click on any sheet name and select view code to open the vba code, then click on insert and click on module, and type the programme into the text box and save the file as shown in the below image.
Sub FindReplace_WB() 'Updateby nirmal Dim ws As Worksheet Dim xFind As String Dim xRep As String Application.ScreenUpdating = False On Error Resume Next xFind = Application.InputBox("Find what", "Find", "", , , , , 2) xRep = Application.InputBox("Replace with", "find", "", , , , , 2) If xFind = "" Then MsgBox "wrong...", vbInformation, "find" Exit Sub End If For Each ws In ThisWorkbook.Worksheets ws.UsedRange.Replace What:=xFind, Replacement:=xRep, LookAt:=xlWhole Next ws On Error GoTo 0 Application.ScreenUpdating = True End Sub
Now use the function key F5 to open a pop-up window, as shown in the below image.
We can find and replace the whole workbook using this pop-up, enter the word in the text box, and click on "OK."
In this tutorial, we used a simple example to demonstrate how we can search the whole workbook in Excel to highlight a particular set of data.
Kickstart Your Career
Get certified by completing the courseGet Started