Search for data across multiple tabs of a workbook VBA

[cmschmitz24]

I have a workbook with 5 different tabs. Each tab is a different report however each report could have the same data in column A on each of them.
I would like to know how to write a VBA code that would search across each worksheet and pull back "text" that would let me know what tab(s) each data is on.

For example:
sheet 1 has a number in column A that is also represented in sheets 2, 3, 4 and 5. I would like the VBA to add a note to sheet 1 in column J K L or M.

If the number in sheet 1 cell A25 is found on sheet 2, then add a note in sheet 1 cell J25 "yes".
"" on sheet 3, then add a note in sheet 1 cell K25 "yes".
"" on sheet 4, then add a note in sheet 1 cell L25 "yes".
"" on sheet 5, then add a note in sheet 1 cell M25 "yes".

Then,
I need this process to do the same for each sheet following.

If the number in sheet 2 cell A25 is found on sheet 3, then add a note in sheet 2 cell X25
"" on sheet 4, then add a note in sheet 2 cell Y25 "yes"
"" on sheet 5, then add a note in sheet 2 cell Z25 "yes"
"" on sheet 1, then add a note in sheet 2 cell V25 "yes"

etc.

Let me know if you need anymore information.

Thanks!
Christina

 

[DanteAmor]

Let me know if you need anymore information.

You could put the cells for sheet.
If the number in sheet 3 cell A25 sheet4 cell ?, sheet5 cell ?, sheet1 cell ?, sheet2 cell ?,
If the number in sheet 4 cell A25 sheet5 cell ?, sheet1 cell ?, sheet2 cell ?, sheet3 cell ?,
If the number in sheet 5 cell A25 sheet1 cell ?, sheet2 cell ?, sheet3 cell ?, sheet4 cell ?,

 

[cmschmitz24]

The data is variable but will always be in column A. This is a VLOOKUP of some kind but I can't figure it out.

 

[DanteAmor]

If the number in sheet 1 cell A25 is found on sheet 2, then add a note in sheet 1 cell J25 "yes".
"" on sheet 3, then add a note in sheet 1 cell K25 "yes".
"" on sheet 4, then add a note in sheet 1 cell L25 "yes".
"" on sheet 5, then add a note in sheet 1 cell M25 "yes".

Then,
I need this process to do the same for each sheet following.

If the number in sheet 2 cell A25 is found on sheet 3, then add a note in sheet 2 cell X25
"" on sheet 4, then add a note in sheet 2 cell Y25 "yes"
"" on sheet 5, then add a note in sheet 2 cell Z25 "yes"
"" on sheet 1, then add a note in sheet 2 cell V25 "yes"

I'm sorry, I don't think I asked well.
You put the examples for sheets 1 and 2, you can finish the examples for sheets 3, 4 and 5.

 

[cmschmitz24]

If the number in sheet 3 cell A25 is found on sheet 4, then add a note in sheet 3 cell R25
"" on sheet 5, then add a note in sheet 3 cell S25 "yes"
"" on sheet 1, then add a note in sheet 3 cell P25 "yes"
"" on sheet 2, then add a note in sheet 3 cell Q25 "yes"

If the number in sheet 4 cell A25 is found on sheet 5, then add a note in sheet 4 cell S25
"" on sheet 1, then add a note in sheet 2 cell P25 "yes"
"" on sheet 2, then add a note in sheet 2 cell Q25 "yes"
"" on sheet 3, then add a note in sheet 2 cell R25 "yes"

If the number in sheet 5 cell A25 is found on sheet 1, then add a note in sheet 5 cell N25
"" on sheet 2, then add a note in sheet 2 cell O25 "yes"
"" on sheet 3, then add a note in sheet 2 cell P25 "yes"
"" on sheet 4, then add a note in sheet 2 cell Q25 "yes"

 

[DanteAmor]

What do you think of the following proposal. The results will be in columns P to T.
Column P always for sheet1, Q for sheet2, R for sheet3, S for sheet4, T for sheet5.
That way you will visually know which column corresponds to which sheet. Examples:

Dante Amor
	A														O	P	Q	R	S	T
1																sheet1	sheet2	sheet3	sheet4	sheet5
2	a2																Yes
3	a3																	Yes
4	a4																		Yes	Yes
5	a5																			Yes
6	a6																Yes
7	a22																Yes			Yes
Sheet1

Dante Amor
	A	B													O	P	Q	R	S	T
1																sheet1	sheet2	sheet3	sheet4	sheet5
2	a2															Yes
3	a32																	Yes
4	a6															Yes
5	a22															Yes				Yes
Sheet2

Use this:
The macro assumes that your data starts in row 2.

Sub SearchData()
  Dim a As Variant, b As Variant, c As Variant, dic As Object
  Dim i As Long, j As Long, shs As Variant, s1 As Variant, s2 As Variant
 
  shs = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")
  Set dic = CreateObject("Scripting.Dictionary")
  dic.CompareMode = vbTextCompare
 
  For s1 = 0 To UBound(shs)
    dic.RemoveAll
    a = Sheets(shs(s1)).Range("A2", Sheets(shs(s1)).Range("A" & Rows.Count).End(3)).Value2
    ReDim c(1 To UBound(a), 1 To 5)
    For i = 1 To UBound(a)
      If a(i, 1) <> "" Then dic(a(i, 1)) = i
    Next i
    For s2 = 0 To UBound(shs)
      b = Sheets(shs(s2)).Range("A2", Sheets(shs(s2)).Range("A" & Rows.Count).End(3)).Value2
      If s2 <> s1 Then
        For j = 1 To UBound(b)
          If b(j, 1) <> "" And dic.exists(b(j, 1)) Then
            c(dic(b(j, 1)), s2 + 1) = "Yes"
          End If
        Next j
      End If
    Next s2
    Sheets(shs(s1)).Cells(2, "P").Resize(UBound(a), 5).Value = c
  Next s1
  MsgBox "End"
End Sub