合并两个Excel表格共同列至新工作表时遇‘对象变量未设置’错误

错误原因 #

出现object variable or with block variable not set错误的核心原因：

• 刚创建的新表格（ListObject）仅包含表头行，没有数据行，此时DataBodyRange对象尚未初始化，直接访问会返回Nothing，触发报错。
• 设置表头的方式错误，应该修改列表列的Name属性，而非给不存在的DataBodyRange赋值。
• 创建新表格时，Range("A1")未指定所属工作表，可能误引用当前活动表的单元格，引发潜在问题。

修正后的代码 #

Sub CombineTablesToNewWorksheet()
    Dim wsNew As Worksheet
    Dim table1 As ListObject, table2 As ListObject, newTable As ListObject
    Dim commonColumns As Variant
    Dim lastRowTable1 As Long, lastRowTable2 As Long
    Dim i As Integer, colName As Variant, newRowObj As ListRow
    
    ' 设置源表格
    Set table1 = ThisWorkbook.Sheets("Sheet1").ListObjects("Table1") ' 替换为你的工作表和表格名
    Set table2 = ThisWorkbook.Sheets("Sheet2").ListObjects("Table2") ' 替换为你的工作表和表格名
    
    ' 指定共同列名
    commonColumns = Array("Column1", "Column2", "Column3") ' 替换为你的共同列名
    
    ' 创建新工作表
    Set wsNew = ThisWorkbook.Sheets.Add
    
    ' 在新工作表创建空表格（仅表头行）
    Set newTable = wsNew.ListObjects.Add(xlSrcRange, wsNew.Range("A1").Resize(1, UBound(commonColumns) + 1), , xlYes)
    
    ' 复制表头到新表格（修改列名属性）
    For i = LBound(commonColumns) To UBound(commonColumns)
        newTable.ListColumns(i + 1).Name = commonColumns(i)
    Next i
    
    ' 获取源表格的行数
    lastRowTable1 = table1.ListRows.Count
    lastRowTable2 = table2.ListRows.Count
    
    ' 追加Table1的共同列数据
    For i = 1 To lastRowTable1
        Set newRowObj = newTable.ListRows.Add
        For Each colName In commonColumns
            newRowObj.Range(newTable.ListColumns(colName).Index).Value = table1.ListColumns(colName).DataBodyRange(i, 1).Value
        Next colName
    Next i
    
    ' 追加Table2的共同列数据
    For i = 1 To lastRowTable2
        Set newRowObj = newTable.ListRows.Add
        For Each colName In commonColumns
            newRowObj.Range(newTable.ListColumns(colName).Index).Value = table2.ListColumns(colName).DataBodyRange(i, 1).Value
        Next colName
    Next i
    
End Sub

关键修改点 #

• 表头设置：将newTable.ListColumns(i + 1).DataBodyRange.Value改为newTable.ListColumns(i + 1).Name，直接设置列名，避免访问不存在的DataBodyRange。
• 指定工作表范围：创建新表格时使用wsNew.Range("A1")，确保引用新工作表的单元格，避免活动表干扰。
• 简化行赋值：添加新行后直接通过newRowObj.Range赋值，无需计算行号，代码更简洁可靠。

内容的提问来源于stack exchange，提问作者Krishnan Iyer