Updating master list from multiple tabs

[sampson11]

I have a workbook with tabs for different jobs
There are "task" tabs and "notes" tabs, those words are found in the name of the tab
The task tabs have multiple rows, with a name assigned to complete the task in that row

I need a master list for each name, on a new tab (3 names in this department = 3 master lists)
That list needs to search only the "task" tabs and compile all the tasks assigned to that name
the task lists will grow over time, and new task tabs will be added over time (task list limited to 100 rows)
I need the master list to update and include new task tabs as they're added

So far, I've only got an index match that can look at one specific task tab and grab all the task rows for one name from that job
I thought about simplifying this and combining the task and notes tabs
but I still need a solution that searches every tab in the workbook and includes new ones as they're created

 

[mumps]

For clarification, you want to loop through all the "task" sheets and create a Master list for each name found in column F of each sheet. The Master list will contain all the tasks from all the "task" sheets for each name. Is this correct?

 

[sampson11]

For clarification, you want to loop through all the "task" sheets and create a Master list for each name found in column F of each sheet. The Master list will contain all the tasks from all the "task" sheets for each name. Is this correct?

Yes sir, all data from the row should be on the master list for each guy. And job name (tab name) added as well

 

[mumps]

And job name (tab name) added as well

Do you want to name the newly created master sheet using the job name and the person's name? Can you please give me an example of what the name would be for Jake?

 

[sampson11]

Do you want to name the newly created master sheet using the job name and the person's name? Can you please give me an example of what the name would be for Jake?

"Jake list" would be good, as the master sheet will contain tasks from all jobs in the workbook
See the new version of the workbook I uploaded in previous reply, it's my work in progress trying to use formulas and a little VBA
I also assume your VBA code would be decipherable enough that I could go in and edit that

 

[mumps]

Yes, it will be a macro. Formulae are excellent, but in your case, the macro will take care of everything. Give me a little time to work it out.

 

[sampson11]

"And job name (tab name) added as well "...

I just meant to add a column that includes the job name since the master sheet will show all tasks from every job

 

[mumps]

Sorry for the delay. The task was rather challenging for me. Try this macro to see if it does what you wanted. It may take several seconds to run so please be patient. If you add additional "task" sheets and run the macro again, it should ignore the previous "task" sheets.

Sub CreateMasterSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, lRow As Long, v As Variant, i As Long, ii As Long, cnt As Long, vName As Variant, sName As String, dic As Object
    Dim k As Variant, arr() As Variant, x As Long
    Set dic = CreateObject("Scripting.Dictionary")
    For Each ws In Sheets
        If ws.Name Like "*Task*" And ws.Name <> "Task Template" Then
            x = x + 1
            ReDim Preserve arr(1 To x)
            arr(x) = ws.Name
            With ws
                lRow = .Range("F" & .Rows.Count).End(xlUp).Row
                v = .Range("F2:F" & lRow).Value
                For i = LBound(v) To UBound(v)
                    If InStr(v(i, 1), "/") > 0 Then
                        vName = Split(v(i, 1), "/")
                        For ii = LBound(vName) To UBound(vName)
                            If Not dic.exists(vName(ii)) Then
                                dic.Add vName(ii), Nothing
                                cnt = cnt + 1
                            End If
                        Next ii
                    Else
                        If Not dic.exists(v(i, 1)) Then
                            dic.Add v(i, 1), Nothing
                            cnt = cnt + 1
                        End If
                    End If
                Next i
            End With
        End If
    Next ws
    For i = LBound(arr) To UBound(arr)
        With Sheets(arr(i))
            lRow = .Range("F" & .Rows.Count).End(xlUp).Row
            For Each k In dic.keys
                .Range("A1").AutoFilter 6, Criteria1:="=*" & k & "*"
                cnt = .[subtotal(103,A:A)] - 1
                If cnt > 0 Then
                    If Not Evaluate("isref('" & k & " " & "List" & "'!A1)") Then
                        Sheets.Add(After:=Sheets(Sheets.Count)).Name = k & " " & "List"
                        Intersect(.Rows("1:" & lRow), .Range("B1:I" & lRow).SpecialCells(xlVisible)).Copy Range("A1")
                        Range("I1:K1") = Array("Job #", "# Tasks", "Job")
                        With Range("I2")
                            .Value = "1"
                            If cnt > 1 Then
                                .AutoFill Destination:=Range("I2").Resize(cnt), Type:=xlFillSeries
                            End If
                        End With
                        .Range("A1").AutoFilter
                        Range("J2").Resize(cnt) = .Cells(.Columns("J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row, "J")
                        Range("K2").Resize(cnt) = arr(i)
                        Range("L2") = k
                        Cells.WrapText = False
                        Columns.AutoFit
                    Else
                        If WorksheetFunction.CountIf(Sheets(k & " " & "List").Range("K:K"), arr(1)) = 0 Then
                            Intersect(.Rows("2:" & lRow), .Range("B1:I" & lRow).SpecialCells(xlVisible)).Copy Sheets(k & " " & "List").Cells(Rows.Count, "A").End(xlUp).Offset(1)
                            With Sheets(k & " " & "List").Cells(Rows.Count, "I").End(xlUp).Offset(1)
                                .Value = Sheets(k & " " & "List").Cells(Rows.Count, "I").End(xlUp) + 1
                                If cnt > 1 Then
                                    .AutoFill Destination:=.Resize(cnt), Type:=xlFillSeries
                                End If
                            End With
                            .Range("A1").AutoFilter
                            Sheets(k & " " & "List").Cells(Rows.Count, "J").End(xlUp).Offset(1).Resize(cnt) = .Cells(.Columns("J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row, "J")
                            Sheets(k & " " & "List").Cells(Rows.Count, "K").End(xlUp).Offset(1).Resize(cnt) = arr(i)
                            Cells.WrapText = False
                            Columns.AutoFit
                        End If
                    End If
                End If
            Next k
        End With
    Next i
    Application.ScreenUpdating = True
End Sub

 

[sampson11]

Thanks man, so cool of you to put your time into this. & I'm glad to give you something to challenge and push your skills too!
I know I enjoy stuff like that, which is why I take on these tasks from other depts where I work. I got the formula method to work, which requires 2000 rows to accommodate 20 potential tabs with 100 tasks each... whew! that was a lot of copy/paste/edit!
I'll apply your macro sometime today and let you know how it goes, Then I'll study it and learn some more VBA for myself