如何用VBA按列名跨工作簿复制表格并保留目标表格格式？

一、修复获取Table1列名的循环问题 #

原代码遍历整行单元格（Rows(2).Cells）会因B1为空提前退出，正确做法是直接遍历Table1的列集合，精准获取列名：

' 获取Table1的列名
Dim TblHeadings() As String
Dim col As ListColumn
Dim i As Integer
i = -1

With wb1.Sheets("Sheet1").ListObjects("Table1")
    For Each col In .ListColumns
        i = i + 1
        ReDim Preserve TblHeadings(i) As String
        TblHeadings(i) = col.Name ' 直接读取列名，无需依赖单元格值
    Next col
End With

这段代码通过ListColumns集合遍历表格所有列，彻底避免了整行遍历的空值陷阱，且获取列名的方式更可靠。

二、原有代码的问题修正 #

原代码存在多处逻辑错误，无法正常运行，修正后的列复制逻辑如下：

' 按列名动态复制指定列
Dim colName As String, copyRange As Range
With wb1.Sheets("Sheet1").ListObjects("Table1")
    For Each colName In TblHeadings
        If copyRange Is Nothing Then
            Set copyRange = .ListColumns(colName).Range
        Else
            Set copyRange = Union(copyRange, .ListColumns(colName).Range)
        End If
    Next colName
End With

' 直接操作目标区域，避免Select/Activate（VBA最佳实践）
With wb2.Sheets("Sheet1").ListObjects("Table2")
    ' 清空目标表格内容
    If Not .DataBodyRange Is Nothing Then .DataBodyRange.Delete
    ' 将复制的列粘贴到目标表格表头位置
    copyRange.Copy Destination:=.Range(.HeaderRowRange.Cells(1))
End With

关键修正点：

• 遍历TblHeadings数组而非单个对象z
• 移除Select/Activate操作，直接通过对象引用操作单元格
• 统一复制逻辑，避免代码片段冲突

三、保留Table2格式的最优方案 #

直接粘贴值会覆盖目标表格格式，最优方案是仅更新数值，完全保留目标表格的原有格式，推荐两种实现方式：

方式1：直接赋值（最高效，无剪贴板依赖） #

Dim srcTbl As ListObject, destTbl As ListObject
Set srcTbl = wb1.Sheets("Sheet1").ListObjects("Table1")
Set destTbl = wb2.Sheets("Sheet1").ListObjects("Table2")

' 清空目标表格数据行
If Not destTbl.DataBodyRange Is Nothing Then
    destTbl.DataBodyRange.Delete
End If

' 匹配源表行数添加新行
If srcTbl.ListRows.Count > 0 Then
    destTbl.ListRows.Add Count:=srcTbl.ListRows.Count, AlwaysInsert:=True
    ' 直接赋值数值，完全保留目标格式
    destTbl.DataBodyRange.Value = srcTbl.DataBodyRange.Value
End If

这种方式绕过剪贴板，直接将源表数值写入目标表，速度快且100%保留目标表格的样式、格式设置。

方式2：选择性粘贴（适合需要保留部分格式的场景） #

如果需要保留源表的数值格式但不覆盖目标表格样式，可使用：

srcTbl.DataBodyRange.Copy
destTbl.DataBodyRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False ' 清除剪贴板

但注意：此方式仅保留数值和数字格式，目标表格的单元格样式、表格主题仍会保留。

内容的提问来源于stack exchange，提问作者actuarial.codes