Find value in workbook written in cell in master sheet and get names of sheets where it was found

matutko4

New Member
Joined
Jan 17, 2024
Messages
9
Office Version
  1. 2021
Platform
  1. Windows
Hello ,

first of all , I am pretty new to VBA and mostly i just copy and paste codes and try to put them together , so i would like to ask for help

i do have workbook with more than 200 sheets , on master sheet called "Domov"
i would like to have search cell where if i write value it will go trough whole workbook and find on which sheets it found the value , than under the search cell it would write sheets names (names of sheets where value was found)

thank you in advance
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
This should do it.

Sub FindStringInAllSheets()
Dim ws As Worksheet
Dim masterSheet As Worksheet
Dim searchWord As String
Dim resultString As String
Dim i As Long

Set masterSheet = ThisWorkbook.Sheets("MasterSheet")

searchWord = masterSheet.Range("A2").Value

resultString = ""

For Each ws In ThisWorkbook.Sheets
If ws.Name <> "MasterSheet" Then
If WorksheetFunction.CountIf(ws.UsedRange, "*" & searchWord & "*") > 0 Then
resultString = resultString & ws.Name & ", "
End If
End If
Next ws

resultString = Left(resultString, Len(resultString) - 2)
masterSheet.Range("A5").Value = resultString
End Sub
 
Upvote 0
Start with a blank sheet and name it "Summary". Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet "Summary" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter the search value in cell A1 and press the RETURN key. The sheet names that contain the search value will be listed in column A. You can change the range (in red) to suit your needs.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet, fnd As Range
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            Set fnd = ws.UsedRange.Cells.Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                Cells(Rows.Count, "A").End(xlUp).Offset(1) = ws.Name
            End If
        End If
    Next ws
End Sub
 
Upvote 0
Start with a blank sheet and name it "Summary". Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet "Summary" and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter the search value in cell A1 and press the RETURN key. The sheet names that contain the search value will be listed in column A. You can change the range (in red) to suit your needs.
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet, fnd As Range
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            Set fnd = ws.UsedRange.Cells.Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                Cells(Rows.Count, "A").End(xlUp).Offset(1) = ws.Name
            End If
        End If
    Next ws
End Sub
Hello ,

thank you for your quick answer , your code work perfectly , there is only one think which I need different ( I did not specify it in my first post) I would like to do the same but for several numbers at once , i uploaded photo so it will be more clear what I mean

1705575827939.png
 
Upvote 0
I'm sorry but I don't follow. It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
I'm sorry but I don't follow. It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
Hello,

I am sorry to make it unclear, I will try to be more specific , please see link to excel file down below ,


I hope it is more clear right now , thank you for your patience

I would like to have table for example as in my workbook , at B4:B13 i would like to enter values , those values can appear on multiple sheets (as it does in my example excel workbook) on the right side of the table C4:H13 I would like to have names of sheets where values from B4:B13 was found. In workbook which I send you I wrote it manually , but I want to automate it

example of usage : person will have several numbers in another excel , they will copy them , paste them to table under "enter values you are looking for" , after that , VBA would automatically fill column C,D,E etc. with name of column where value was found (depends on how many sheets value was found)

1​
ABCDABCD
2​
3​
enter values you are looking forentered value in B3:B13 was found in this sheets :
4​
11sheet1
5​
12sheet1
6​
22sheet1sheet2
7​
23sheet1sheet2
8​
41sheet4
9​
61not found
10​
11​
12​
13​


I hope it is more clear right now , thank you for your patience
 
Upvote 0
Try this macro in the worksheet code module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet, fnd As Range, rng As Range, lRow As Long
    For Each rng In Target
        For Each ws In Sheets
            If ws.Name <> "mastersheet" And ws.Name <> "mastersheet (2)" Then
                Set fnd = ws.Range("B:B").Find(rng.Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    Cells(rng.Row, Columns.Count).End(xlToLeft).Offset(, 1) = ws.Name
                End If
            End If
        Next ws
    Next rng
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("C4:C" & lRow).SpecialCells(xlBlanks) = "Not Found"
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this macro in the worksheet code module:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim ws As Worksheet, fnd As Range, rng As Range, lRow As Long
    For Each rng In Target
        For Each ws In Sheets
            If ws.Name <> "mastersheet" And ws.Name <> "mastersheet (2)" Then
                Set fnd = ws.Range("B:B").Find(rng.Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    Cells(rng.Row, Columns.Count).End(xlToLeft).Offset(, 1) = ws.Name
                End If
            End If
        Next ws
    Next rng
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("C4:C" & lRow).SpecialCells(xlBlanks) = "Not Found"
    Application.ScreenUpdating = True
End Sub
Hello,

thank you , it works , but only for one time use , when i tried to delete the cells so i can put there new values it makes fancy stuff

everyday values in every sheet changes , so i need to have possibility to copy new values in column B
 
Upvote 0
Try:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim ws As Worksheet, fnd As Range, rng As Range, lRow As Long
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("C4:H" & lRow).ClearContents
    For Each rng In Target
        For Each ws In Sheets
            If ws.Name <> "mastersheet" And ws.Name <> "mastersheet (2)" Then
                Set fnd = ws.Range("B:B").Find(rng.Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    Cells(rng.Row, Columns.Count).End(xlToLeft).Offset(, 1) = ws.Name
                End If
            End If
        Next ws
    Next rng
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    Range("C4:C" & lRow).SpecialCells(xlBlanks) = "Not Found"
    Range("B4", Range("B" & Rows.Count).End(xlUp)).ClearContents
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hello ,

thank you very much , we are almost there , the only issue right now is that values in B are disappearing , please see video on link down below

 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top