Search multiple sheets, return entire rows under each sheet name

Hi Black Duck

Welcome to the forum...If I understand correctly then this is one option...
Only "Current tasks" - Right

This code in sheets("Your Task").Module

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Application.EnableEvents = False
        Who = Target
        If Not Who = "" Then Macro
        Application.EnableEvents = True
    End If
End Sub

And this in a stand alone Module...

Option Explicit
Public Who As String
Sub Macro()
Dim Arr, Temp, Hdr As Boolean, ws As Worksheet, fnd As Range
Dim i As Long, lr As Long, cnt As Long
Application.ScreenUpdating = False
Hdr = False: cnt = 0
ReDim Temp(1 To 1000, 1 To 3) ' !Change to accommodate...
With Sheet3
    lr = .Cells(.Rows.Count, 1).End(xlUp).Row
    If lr = 1 Or lr = 2 Then lr = 3
    .Range("A3:A" & lr).EntireRow.Delete
    For Each ws In ThisWorkbook.Sheets
        If ws.Name Like "Project*" Then
            With ws
                lr = .Cells(.Rows.Count, 1).End(xlUp).Row
                Set fnd = .Range("A:G").Find("Current Tasks", LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    Arr = .Range("A" & fnd.Row + 1 & ":C" & lr).Value
                    For i = LBound(Arr) To UBound(Arr)
                        If Arr(i, 1) = Who Then
                            cnt = cnt + 1
                            If Hdr = False Then
                                Temp(cnt, 1) = ws.Name
                                cnt = cnt + 1
                                Hdr = True
                            End If
                            Temp(cnt, 1) = Arr(i, 1)
                            Temp(cnt, 2) = Arr(i, 2)
                            Temp(cnt, 3) = Arr(i, 3)
                        End If
                    Next i
                End If
            End With
        End If
        Hdr = False
    Next ws
    .Range("A4").Resize(cnt, 3) = Temp
End With
Application.ScreenUpdating = True
End Sub

I notice the use of 'Project *' for the sheet names per my sample data, but this was meant to signify that each project has a different name, so unfortunately it won't work at this stage

Firstly, have you tested it on your uploaded sample before making the above statement...
Secondly, No it won't work if your actual file setup is different to your sample upload...as the code is written for your initial requirement...
To rectify this you need to Upload a sample file depicting actual sheet names...
In future, also, to ensure accuracy and no time wasted on code, please upload actual file setup sample and explain in detail what your requirement is...

OR

If the only sheets that need to be excluded are INDEX and Your Tasks then

Replace this line...

If ws.Name Like "Project*" Then

With ...

If ws.Name <> "INDEX" And ws.Name <> "Your Tasks" Then

Unfortunately not on the working version

Is the SET-UP at least the same as your sample...Have you changed the line of code that I suggested in Post 4

Can you elaborate...What is it not doing...Can only help you if you are prepared to be more transparent...

My apologies sintek
Just to clarify, YES your code WORKS for the sample. Unfortunately not on the working version

As per post 6 which was not answered...

Is the SET-UP at least the same as your sample

Can only help you if you are prepared to be more transparent...

I am not at liberty to divulge client identities, projects, and/or subcontractor names.

Could go around in circles forever...Your actual file is not then setup as per your sample so unfortunately if you aren't prepared to come to the party, so to speak, and upload a sample file depicting your actual setup desensitizing the information - then I, or no one else for that matter, will be able to help...

Good luck...

Hi Black Duck

Your post #1 refers...

I want to be able to create a separate list of all tasks assigned to a selected worker

My response in Post #2 refers

Only "Current tasks" - Right

Hence this snippet within the code...

Set fnd = .Range("A:G").Find("Current Tasks", LookIn:=xlValues, lookat:=xlWhole)

Your sample 2 upload has this code changed to...

Set fnd = .Range("A:G").Find("SB", LookIn:=xlValues, lookat:=xlWhole)

May I ask why...

Changing that snippet back to original solves...
When I select WHO from cell [A2] in "Your tasks" sheet it lists all the "CURRENT TASKS" for that selected WHO...

I think the best option is for you to upload a sample file once again with the expected result required displayed...This will ensure accuracy...

Black Duck Black Duck Black Duck...

Just trying to help so that in future you know exactly what you need to do to expedite assistance...So the next time round, no time is wasted on unnecessary code...

tasks within that project that are assigned to that worker

...additionally, 'all tasks' includes ANY row of data on each sheet that has the WHO in Column A

Well...If you had started with this then this thread could have been solved days ago...
My response in Post #2 refers

Only "Current tasks" - Right

Next...Your expected results is incorrect...
WFC has more than just one entry...It has 3
Am Warehouse is actually Am WH
SWaRA includes entries for AB/JT as well ...You did not mention that shared employee tasks should also be extracted...

Lastly which should actually be the most important...Get rid of your merged cells...They just create havoc with VBA...

I have attached a file housing the new code...with the correct results extracted...
Have only extracted columns A to C...

.............................................
Thanks.gif
Remember-Mark thread as solved