复制到Excel表格（ListObject）

• 简单的回答是，在这个例子中，ListRow有四列，而您只给它分配了一个两列的数组，从答案看，您自己已经得出了结论（.Resize(, 2)）。
  进步

Option Explicit

Sub AddStudents()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim wsSports As Worksheet: Set wsSports = wb.Sheets("Sports")
    Dim Sports(): Sports = wsSports.Range("C3:C5").Value
    Dim SportsCount As Long: SportsCount = UBound(Sports, 1)
    
    Dim wsStudents As Worksheet: Set wsStudents = wb.Sheets("Students")
    Dim loStudents As ListObject: Set loStudents = wsStudents.ListObjects(1)
    Dim lcStudents As ListColumn: Set lcStudents = loStudents.ListColumns(1)
    Dim rgStudents As Range: Set rgStudents = lcStudents.DataBodyRange
    
    Dim wsOK As Worksheet: Set wsOK = wb.Sheets("OK")
    Dim loOK As ListObject: Set loOK = wsOK.ListObjects(1)
    Dim lcOK As ListColumn: Set lcOK = loOK.ListColumns(1)
    Dim rgOK As Range: Set rgOK = lcOK.DataBodyRange
    
    Dim cell As Range, Student, MatchOK, r As Long, IsNotStudentAdded As Boolean
    
    For Each cell In rgStudents.Cells
        If rgOK Is Nothing Then
            IsNotStudentAdded = True
        Else
            MatchOK = Application.Match(cell.Value, rgOK, 0)
            If IsError(MatchOK) Then IsNotStudentAdded = True
        End If
        If IsNotStudentAdded Then
            Student = cell.Value
            For r = 1 To SportsCount
                loOK.ListRows.Add.Range.Resize(, 2).Value _
                     = Array(Student, Sports(r, 1))
            Next r
            IsNotStudentAdded = False
            Set rgOK = lcOK.DataBodyRange
        End If
    Next cell

    MsgBox "Students added.", vbInformation

End Sub

所以我决定彻底修改我的宏以避免任何错误：

Sub Macro7()
Dim N, S, i, j
Application.ScreenUpdating = False
N = Range("Tableau1"): S = Sheets("Sports").Range("C3:C5").Value
With Range("Tableau2").ListObject
  If .ListRows.Count > 0 Then .DataBodyRange.Delete
  For Each i In N
    For Each j In S
      .ListRows.Add.Range.Resize(, 2) = Array(i, j)
    Next
  Next
End With
End Sub