Macro To Loop Through Worksheets To Match Value And Return All Results

[rameezl17]

Hi Everyone,

I am trying to build a macro that takes the value in Sheet name - "Report" Cell "A2" (this is going to be a employees name), looks through all the sheets in my workbook and returns each project name that their name is in one of the sheets going down from Cell "A5". So If their name appears 3 times within all the tabs in my workbook I want it to return the project name which will be in Cell E20 in all the other tabs.

So if I were to do this within excel and NOT VBA it would look like this -
=INDEX('1'!E20:E24,MATCH('Report'!A2,'1'!$G$20:$G$24))

The '1' is the name of the tab that it has the info i need to match to. (All the worksheets are numbered 1,2,3,4,5, and will continue growing as more projects come in)
I need the above formula to loop through all workbooks and once it finds a match to post the result in cell A5 and keep going down from there for each match that it finds

Thank you for your help!

 

[DataBlake]

oh wait math isn't my strong suit

Sheets("Report").Range(Cells(5 + p, 1), Cells(9 + p , 1)).Value = Sheets(i).Range("F20:F24").Value
p = p + 5

one of these will work eventually

 

[rameezl17]

Hmm.. seems like its just copy and pasting all the cells from F20:F24 from all the sheet tabs rather than matching it to the name from Report(A2) and giving me their role in the project from F20:F24

 

[DataBlake]

Hmm.. seems like its just copy and pasting all the cells from F20:F24 from all the sheet tabs rather than matching it to the name from Report(A2) and giving me their role in the project from F20:F24

well thats exactly what its doing

So the activesheet which is "Report"you want to take the value of A2 and you want to loop through each sheet that is not "Report"
If the name from Report matches anywhere on "Tab" sheets 3 times you want to return cell E20 to A5 of sheet "Report"

am i understanding that correctly?

^ this is what i made
in sheets '1' '2' '3' etc where is the role? in the F column?
i.e if i find a name 3 times you want the current row's F column?
perhaps you can re-iterate what you want to happen?
i can't see your sheets so i have no idea where things are i'm just going off of what you typed in your first post.

so basically heres the important info i need clarification on:
1. where are project roles located? Include sheet names, which role is in which ranges
2. Explain in detail this quoted line; i was under the impression that you wanted to see if it appears 3 times in each sheet, but after reading it again you want to see if it appears 3 times total through all of the sheets?

So If their name appears 3 times within all the tabs in my workbook I want it to return the project name which will be in Cell E20 in all the other tabs.

3. where do you want the project roles to be returned? Cells A5-A9, Cells A10-A14, etc?
4. where are the names relative to the project roles on the tab sheets or vice versa?

 

[rameezl17]

Yeah sorry I know this is confusing without the workbook...

1. Project roles are located in the numbered tabs (1,2,3,4,5,etc) [forever growing list] in Cells F20:F24
2. I mis-worded that line. Everytime someones name exists just once in the numbered tabs then I want them to return the project name from Column E20 in the Numbered Tabs
3. So far the "Report" Tab has the Project names in column A, I would like the roles to be in Column B
4. The name comes from The Report Tab which is the name I need to be looked for in the numbered tabs and then return the Project Name(Cell E20) and then their role in the respective project(Cell F20:F24)

Does this clarify things a bit?

 

[DataBlake]

Does this clarify things a bit?

yes so would you say your example would like like this if "Boofles" was found in 3 sheets?

	A	B
1
2	Boofles
3
4
5	PROJECT NAME 1	PROJECT LEAD (F20)
6		PROJECT Support (F21)
7		PROJECT Support (F22)
8		PROJECT Support (F23)
9		PROJECT Support (F24)
10	PROJECT NAME 2	PROJECT LEAD (F20)
11		PROJECT Support (F21)
12		PROJECT Support (F22)
13		PROJECT Support (F23)
14		PROJECT Support (F24)
15	PROJECT NAME 3	PROJECT LEAD (F20)
16		PROJECT Support (F21)
17		PROJECT Support (F22)
18		PROJECT Support (F23)
19		PROJECT Support (F24)

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Report​

 

[rameezl17]

Ideally i would want my report tab to look like this:

If "Rami was found in 2 out of the 3 sheets

A B
1
2 Rami
3
4
5 Project 1 Project Lead
6 Project 2 Project Support

 

[rameezl17]

So column B should only show where "Boofles" is in the numbered tabs. So if in Project 1 Boofles was the Project Support then Column B5 should just only say Project Support. Then In Column A6 it will go to the next project and Column B6 will say his role depending where he is located in the numbered tabs

 

[DataBlake]

So column B should only show where "Boofles" is in the numbered tabs. So if in Project 1 Boofles was the Project Support then Column B5 should just only say Project Support. Then In Column A6 it will go to the next project and Column B6 will say his role depending where he is located in the numbered tabs

OOOOOOOOOOOOH
so if we find Boofles in F23 you want "Project Support3" physically returned in B?

So lets say we have 6 sheets Boofle appears in
'1' - Cell F20
'3' - Cell F22
'5' - Cell F21

you would want the output like this?

	A	B
1
2	Boofles
3
4
5	PROJECT NAME 1	Project Lead
6	PROJECT NAME 3	Project Support2
7	PROJECT NAME 5	Project Support

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Report
​

 

[rameezl17]

Yesssssssssssssssssssssssssss! only show me the projects and roles that Boofles is found in, for all the numbered tabs

 

[DataBlake]

Yesssssssssssssssssssssssssss!

theres DEFINITELY a better way to write this code but, but im lazy

Sub timesTHREE()
Dim x As Long, i As Long, j As Long, k As Long, p As Long
Dim ary1 As Variant, key As Variant
Dim wsCOUNT As Long
Dim ws As Worksheet
Dim lastROW As Long, lastCol As Long


wsCOUNT = Application.Sheets.Count

'loops through the sheets
For i = 7 To wsCOUNT
    k = 0

'gets the sheets last row and last column
lastROW = Sheets(i).Range("A" & Rows.Count).End(xlUp).Row
lastCol = Sheets(i).Range("A1").SpecialCells(xlCellTypeLastCell).Column

'sets the current sheet as the array given there are no blank rows/columns
ReDim ary1(1 To lastROW, 1 To lastCol)
ary1 = Sheets(i).Range("A1").CurrentRegion.Value2

'loop through the rows of the array
For j = LBound(ary1) To UBound(ary1)
    
'find  matches between A2 and array
If Sheets("Report").Range("A2").Value = ary1(j, 6) Then

'loop if to find project supports
    For k = 1 To 4
        If ary1(j, 6) = ary1(20 + k, 6) Then
            Sheets("Report").Cells(5 + x, 1).Value = ary1(20, 5)
            If k <> 1 Then
            Sheets("Report").Cells(5 + x, 2).Value = "Project Support" & k
            x = x + 1
            Else
            Sheets("Report").Cells(5 + x, 2).Value = "Project Support"
            x = x + 1
            End If
        
        End If
    Next k
          
'if to find project leads
If ary1(j, 6) = ary1(20, 6) Then
    Sheets("Report").Cells(5 + x, 1).Value = ary1(20, 5)
    Sheets("Report").Cells(5 + x, 2).Value = "Project Lead"
    x = x + 1
Else
End If
End If
        
Next j
Next i

End Sub

i made an edit because E20 wasnt returned it was E(row match)