Adding x number of rows based on a fixed criteria

If all corresponding families are in tab name ending with " family"
try below code:

HTML Code: 

Option Explicit
Sub family()
Dim lr&, k&, cell As Range, cellb As Range, ws As Worksheet, fName As String, arr(1 To 100000, 1 To 3)
With Worksheets("Participants")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    For Each cell In .Range("A2:A" & lr)
        For Each ws In Sheets
            If InStr(1, ws.Name, " family") Then
                fName = Trim(Left(ws.Name, InStr(1, ws.Name, " family") - 1))
                If ws.Name <> .Name And cell = fName Then
                    k = k + 1
                    arr(k, 1) = fName
                    For Each cellb In ws.Range("A1:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
                        k = k + 1
                        arr(k, 2) = cellb.Value
                        arr(k, 3) = cellb.Offset(0, 1).Value
                    Next
                End If
            End If
        Next
    Next
' for testing, paste results from cell B2. USe A2, if you want to overwrite current data
.Range("B2:C10000").ClearContents
.Range("B2").Resize(k, 3).Value = arr
End With
End Sub

You are really on to something! I tried it out and it works perfectly for specific columns (A+B).

I would need to apply it for the entire rows, as the final dataset I am planning to work with is much larger (A:BN). To illustrate I expanded the example. Would you know how to proceed with the full rows?

Excel_challenge2.PNG

Could you attach a dummy worksheet with full columns/rows?

This proposal employs Get & Transform (AKA Power Query).
The data ranges on each sheet are converted into tables.
The following Advanced Editor code is applied:

Please Login or Register  to view this content.

Note that AFTER the table is placed on the OutputTable sheet it is sorted ascending based on the Family Name column.
Let us know if you have any questions.