Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
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.
Step 1
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.
Program
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
Step 2
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."
Conclusion
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.
400 Views
