Generate result from tables

harudaaa 

First, a few words about the terminology.  What you have (in Excel terms) are not tables, but lists, specifically, you have lists in ranges (i.e., ranges of cells).  Excel allows you to include what are called tables that are single units, with column headers, usually with borders and specially colored rows, but always containing a special marker in the lower-right corner in the last column of the last row.  That marker allows you to easily increase (or decrease) the number of rows in the table.  In the workbook that I am attaching, Sheet1 has your lists as ranges (approximately like yours, but different columns), while Sheet2 has your lists in tables.  Tables can be easier to maintain, but are a little more difficult to learn about and thus to write code for.  (And Excel also has something different called data tables, which are not the same as any table that contains data but I won't talk further about those.)

With your lists in ranges, you have to reference the content of your lists using Excel's column letters  (typically) and row numbers counting from 1 at the top of the worksheet.  (With Excel tables, you reference the content using the column names and the data row numbers counting from 1 at the top of the table data.)

You already have a form button, so you likely already know that to create and/or edit the VBA code that it executes, you can right-click the button to get the context menu (a.k.a. popup menu), and left-click the menu item "Assign macro..." 

Within the macro, you will need the code to know or determine what the top and bottom of your data rows are.  So, with regard to your Team Member list, the code might be:

Dim in4TopOfTeamMemberData  As Long
Dim in4BottomOfTeamMemberData   As Long
'   If the top of the data will probably always be in the same row,
'   you can "hard-code" (i.e., use a literal value for) the row number:
in4TopOfTeamMemberList = 3
'   There are multiple ways to determine the bottom of the data.
'   This technique looks upward from the bottom of the spreadsheeet
'   to find the first-encountered non-blank cell in column B:
in4BottomOfTeamMemberData = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row

Note that you do not have to prefix your variable names (as I do) with in4 for Long variables and str for String variables, but it's a good practice to do that or something similar; it helps the person reading the code in the future (including you) remember what type of data the variable represents.

And of course you would write similar code to determine the top and bottom rows of the other lists.  Later (within the same macro of course) the VBA code needs to loop through the entries in the Team Member range to examine the presence of each member and take action appropriately.  So that code might look like:

Dim in4TMRow    As Long
Dim strName     As String
'   For each row in the Team Member data...
For in4TMRow = in4TopOfTeamMemberData To in4BottomOfTeamMemberData
    '   Pick up the name of this member.
    strName = Range("B" & in4TMRow).Value
    '   If the member is present...
    If Range("C" & in4TMRow).Value = "Present" Then
        '   Copy the team member's name to one of the two lists.
        'TODO: Add code here to do this.
    End If
Next in4TMRow

You now need additional VBA code to place that member's name into one of the remaining lists.  Upon writing this much code in this macro, you might choose to separate that additional code into its own procedure (either Sub or Function) and include just a statement or two here (in place of the TODO line) to call (a.k.a. invoke) that other procedure.  But for this example, I'll assume the additional code replaces the 10th line of that code fragment:

        Dim in4CoRow    As Long
        Dim blnMemberWasPlaced  As Boolean
        blnMemberWasPlaced = False  '(at this point while processing)
        For in4CoRow = in4TopOfCounterData To in4BottomOfCounterData
            If Range("F" & in4CoRow).Value = "Open" Then
                'TODO: Add code to decide where in the Roster to place
                '   this member; for example, if the same row as the
                '   open Counter:
                Range("I" & in4CoRow).Value = strName
                blnMemberWasPlaced = True   '(Yay!)
                '   ...and then probably:
                Range("F" & in4CoRow).Value = "Closed"
            End If
        Next in4CoRow
        If blnMemberWasPlaced = False Then
            'TODO: Add code to place the member in the Spare range
        End If

That should get you started.