Excel Guides

Searching Through Many Workbooks in Excel

There are a few different ways that you can search through many workbooks in Excel. One way is to use the Find and Replace feature. To do this, click on the Home tab, then click on Find & Select in the Editing group. A drop-down menu will appear. Click on Find. In the Find and Replace dialog box that appears, click on the Options button. This will open up more options for your search. Make sure that the Workbook option is selected and then enter the text that you want to search for in the Find what field. You can also specify any other options that you want, such as whether you want to match case or match entire cell contents. Click on the Find All button and a list of all of the cells that contain your search criteria will appear in the Results pane. You can then click on any of the cells in the list to go to that cell in its respective workbook. Another way to search through many workbooks is to use VBA code. For example, let's say that you have a folder full of workbooks and you want to search through all of them for a certain value. The code below will loop through all of the workbooks in a specified folder and look for a value in column A of each workbook. If it finds the value, it will return the workbook name and sheet name where the value was found.

Sub SearchThroughWorkbooks()
Dim wb As Workbook
Dim strFolder As String
Dim strValue As String
strFolder = "C:\Users\User\Documents\Excel Files\"
strValue = "Search Value"
For Each wb In Workbooks
If wb.Name Like "*.xls*" Then
Set sht = wb.Sheets("Sheet1")
Set rng = sht.Range("A:A")
Set c = rng.Find(What:=strValue, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox "Found " & strValue & " in " & wb.Name & ", Sheet " & sht.Name & ", Cell " & c.


" '& c.
" '& c.
" '& c.
" '& c.
" '& c.
" '& c.
" '& c.
" '& c.
" '& c.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.