How to quickly search a value in multiple sheets or workbooks?
Have you ever imaged to search a certain value in multiple sheets or workbooks in Excel? This tutorial introduces some different methods to solve the problems about searching in multiple sheets or searching in multiple workbooks.
With Excel’s Find and Replace function, you can find a certain value across multiple sheets.
1. Select multiple sheet tabs you want to find value from by holding the Ctrl key and clicking the worksheets in the Sheet Tab bar one by one. See screenshot:
2. Then Press Ctrl + F to enable the Find and Replace window, and type the value you want to search in the Find what textbox under Find tab, and then click Find All button to list all the results. See screenshot:
Find and replace value across sheets and workbooks
|Kutools for Excel's advanced Find and Replace function, can help you find and replace a value across multiple sheets and opened workbooks. Free Download|
|Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
If you want to search a value in all closed workbooks from a folder, you only can apply a VBA to solve it.
1. Enable a new workbook and select a cell, then press Alt + F11 keys to open Microsoft Visual for Basic Applications window.
2. Click Insert > Module and paste below VBA to the new Module window.
VBA: Search a value across all workbooks of a folder.
Sub SearchFolders() 'UpdatebyKutoolsforExcel20200913 Dim xFso As Object Dim xFld As Object Dim xStrSearch As String Dim xStrPath As String Dim xStrFile As String Dim xOut As Worksheet Dim xWb As Workbook Dim xWk As Worksheet Dim xRow As Long Dim xFound As Range Dim xStrAddress As String Dim xFileDialog As FileDialog Dim xUpdate As Boolean Dim xCount As Long Dim xAWB As Workbook Dim xAWBStrPath As String Dim xBol As Boolean Set xAWB = ActiveWorkbook xAWBStrPath = xAWB.Path & "\" & xAWB.Name On Error GoTo ErrHandler Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker) xFileDialog.AllowMultiSelect = False xFileDialog.Title = "Select a forlder" If xFileDialog.Show = -1 Then xStrPath = xFileDialog.SelectedItems(1) End If If xStrPath = "" Then Exit Sub xStrSearch = "KTE" xUpdate = Application.ScreenUpdating Application.ScreenUpdating = False Set xOut = Worksheets.Add xRow = 1 With xOut .Cells(xRow, 1) = "Workbook" .Cells(xRow, 2) = "Worksheet" .Cells(xRow, 3) = "Cell" .Cells(xRow, 4) = "Text in Cell" Set xFso = CreateObject("Scripting.FileSystemObject") Set xFld = xFso.GetFolder(xStrPath) xStrFile = Dir(xStrPath & "\*.xls*") Do While xStrFile <> "" xBol = False If (xStrPath & "\" & xStrFile) = xAWBStrPath Then xBol = True Set xWb = xAWB Else Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False) End If For Each xWk In xWb.Worksheets If xBol And (xWk.Name = .Name) Then Else Set xFound = xWk.UsedRange.Find(xStrSearch) If Not xFound Is Nothing Then xStrAddress = xFound.Address End If Do If xFound Is Nothing Then Exit Do Else xCount = xCount + 1 xRow = xRow + 1 .Cells(xRow, 1) = xWb.Name .Cells(xRow, 2) = xWk.Name .Cells(xRow, 3) = xFound.Address .Cells(xRow, 4) = xFound.Value End If Set xFound = xWk.Cells.FindNext(After:=xFound) Loop While xStrAddress <> xFound.Address End If Next If Not xBol Then xWb.Close (False) End If xStrFile = Dir Loop .Columns("A:D").EntireColumn.AutoFit End With MsgBox xCount & " cells have been found", , "Kutools for Excel" ExitHandler: Set xOut = Nothing Set xWk = Nothing Set xWb = Nothing Set xFld = Nothing Set xFso = Nothing Application.ScreenUpdating = xUpdate Exit Sub ErrHandler: MsgBox Err.Description, vbExclamation Resume ExitHandler End Sub
3. Then press F5 key or Run button to execute this VBA, and a Select a folder dialog pops out to remind you to select a folder which you want to search value from. See screenshot:
4. And then click OK and another dialog pops out to remind you the number of cells have been found. See screenshot:
5. Click OK to close it, and all the found cells are list in the current worksheet with the corresponding information.
Tip: In above VBA, you search the value “KTE”, and you can change “KTE” from this xStrSearch = "KTE" to another value as you need.
If you just want to search a value across multiple opened workbooks, you can use Kutools for Excel’s advanced Find and Replace utility.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
After free installing Kutools for Excel, please do as below:
1. In one of the opened workbooks, click Kutools > Navigation, and then click the Find and Replace button to go to Find and Replace pane. See screenshot:
2. Then click Find tab, and type the value you want to search in the Find what textbox, and then select All workbooks from the Within dropdown list, and go to click Find All to list all found cells. See screenshot:
With Kutools for Excel’s advanced Find and Replace utility, you can search and replace value in selected sheets across multiple workbooks, all workbooks, active workbook, active sheet or selection.
- How to quickly find and replace across multiple worksheets or the whole workbook?
- How to quickly find and replace in multiple opened Excel files?
- How to find largest negative value (less than 0) in Excel?
- How to extract all duplicates from a column in Excel?