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

rameezl17

Board Regular
Joined
Mar 6, 2018
Messages
105
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!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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:
Upvote 0
Yes, if theres only 1 match then itll return that into cell A5, if it finds another match then continue going down from A5
 
Upvote 0
Also do sheets "1" "2" "3" etc contain any blank rows/columns?
 
Upvote 0
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?
 
Upvote 0
I kinda need them to be numbered 1,2,3,etc because its a ID name given when a new project is created
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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