Kind of hard to understand exactly what you're looking for here. Are you just looking to go through all the rows of INPUT, and insert them all into Sub, Cust, NewUser, etc. whenever you run the VBA macro -- but only when/where the column headers match, and with blanks where they don't?
IMO this does sound like the sort of thing that might be better suited for Power Query, which lets you manipulate tables of data in a more powerful, less "manual" way. If you're looking to eschew that and use VBA though... maybe something like this?
VBA Code:
Sub InsertRowsInAllTables()
Dim Master As ListObject
Set Master = Sheets("INPUT").ListObjects("INPUT")
' Calls InsertRowsInTable for each of the Target tables below
InsertRowsInTable Master, Sheets("Sub").ListObjects("Sub")
InsertRowsInTable Master, Sheets("Cust").ListObjects("Cust")
InsertRowsInTable Master, Sheets("NewUser").ListObjects("NewUser")
InsertRowsInTable Master, Sheets("StudentEnroll").ListObjects("Enroll")
InsertRowsInTable Master, Sheets("Attributes").ListObjects("Attributes")
InsertRowsInTable Master, Sheets("Audit").ListObjects("Audit")
End Sub
Private Sub InsertRowsInTable(Master As ListObject, Target As ListObject)
Dim Indices() As Long
Dim oSource As ListColumn
Dim oColumn As ListColumn
Dim oRow As ListRow
Dim vData As Variant
Dim i As Long
ReDim Indices(1 To Target.ListColumns.Count)
' For each column in the Target table, get its column # in the Master table
On Error Resume Next
For Each oColumn In Target.ListColumns
Set oSource = Master.ListColumns(oColumn.Name)
If Err Then
Indices(oColumn.Index) = 0
Err.Clear
Else
Indices(oColumn.Index) = oSource.Index
End If
Next oColumn
On Error GoTo 0
' Add rows from Master table to Target table
For Each oRow In Master.ListRows
ReDim vData(1 To Target.ListColumns.Count)
For i = 1 To Target.ListColumns.Count
If Indices(i) > 0 Then vData(i) = oRow.Range(Indices(i)).Value
Next i
With Target.ListRows.Add
.Range.Value = vData
End With
Next oRow
End Sub
If what you're trying to do is
look up rows from INPUT which may or may not already be in Sub, Cust, etc., based on the fields they have in common, then that's a different matter. That case would be even more strongly suited for Power Query, or some other tool more intended for structured data manipulation.
You also referred to the "updated" tables as "worksheets" in one instance / "workbooks" in another. For the VBA code, it makes a difference whether they are in fact just separate worksheets within the same workbook, or entirely separate workbooks with their own files. I assumed it was the former; if not, then you would need to include references to those workbooks in the code, e.g. replace
InsertRowsInTable Master, Sheets("Sub").ListObjects("Sub") with something like
InsertRowsInTable Master, Workbooks("Sub.xlsx").Sheets("Sub").ListObjects("Sub"). In a similar vein, if the macro won't live in the same workbook with the INPUT table, then the same idea applies to the references to that table as well.
