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

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top