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

rameezl17

Board Regular
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!
 

BlakeSkate

Active Member
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.
...

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
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?
 
Last edited:

rameezl17

Board Regular
Yes, if theres only 1 match then itll return that into cell A5, if it finds another match then continue going down from A5
 

BlakeSkate

Active Member
cool. one more thing. the naming convention of sheets being "1" "2" "3" is not ideal and will cause problems with checking if those sheets exist. Are they able to be named "Tab1" "Tab2" "Tab3" instead?
 

rameezl17

Board Regular
I kinda need them to be numbered 1,2,3,etc because its a ID name given when a new project is created
 

BlakeSkate

Active Member
I kinda need them to be numbered 1,2,3,etc because its a ID name given when a new project is created
okay well when i make a loop and ask VBA if sheets(x) exists it will treat it as the first sheet rather than the sheet named "1"
so if i'm doing an alternative approach are there are sheets besides "Report" that i need to ignore? maybe a sheet that may contain a persons name 3 times that aren't the tabs?
 

rameezl17

Board Regular
Yes could you use this function?

wsCOUNT = Application.Sheets.Count
For i = 7 To wsCOUNT

since all those numbered tabs come after one of another in order, and all the other tabs that i dont want the match are before them.

if you cant use the above macro then the tab names that need to be ignored are "Project Overview", "Validation Lists", "New Project Template", "Capacity Dashboard", "Database"
 
Last edited:

BlakeSkate

Active Member
try this

Code:
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 coiumns of the array
For x = 1 To UBound(ary1, 2)

'loop through the rows of the array
For j = LBound(ary1) To UBound(ary1)
    
    'find  matches between A2 and array then count them
    If Sheets("Report").Range("A2").Value = ary1(j, x) Then
    k = k + 1
    End If


Next j
Next x
   
'if there were 3 and more paste the value to the range
If k >= 3 Then
Sheets("Report").Cells(5 + p, 1).Value = Sheets(i).Range("E20").Value
p = p + 1
End If

Next i
End Sub
 

Some videos you may like

This Week's Hot Topics

Top