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.


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."


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.

Updated on: 09-Jan-2023


Kickstart Your Career

Get certified by completing the course

Get Started