Could you help me, I would like to create an hyperlink to each cell where the value was found.
Thanks in advance
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.
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 60 days.|
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:
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() 'UpdatebyKutoolsforExcel20151202 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 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 <> "" Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False) For Each xWk In xWb.Worksheets 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 Next xWb.Close (False) 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.
Tabbed browsing & editing multiple Word documents/Excel workbooks as Firefox, Chrome, Internet Explore 10!
You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Word documents or Excel workbooks in one Word window or Excel window, and easily switch between them by clicking their tabs.