Excel VBA选中指定工作表除前3行数据报1004错误及粘贴问题

• 核心问题是Range对象的父工作表与传入的单元格对象父工作表不匹配：
  你写的Set rng = ws.Range(Cells(4, 1).Address(), Cells(lr, 18).Address())代码中，ws.Range明确指定区域所属工作表为output，但Cells(4, 1)、Cells(lr, 18)未加ws.限定，默认指向当前活动工作表的单元格。VBA不支持直接创建跨两个不同工作表的Range对象，因此直接抛出1004错误。
• 移除ws.限定后代码可运行，是因为此时Range和Cells的父对象默认均为活动工作表，不存在跨表引用问题，但只能操作当前激活的工作表，无法满足指定工作表操作需求。

你提出的「复制源区域→定位结构化表格首个数据行首单元格→选择性粘贴值」的核心逻辑完全可行，只需要补充几个细节避免隐性bug：

• 全程不需要使用Select/Activate选中工作表或区域，直接操作VBA对象即可，运行效率更高，也不会触发跨表选择错误
• 所有单元格、区域对象必须显式绑定父工作表，避免默认指向活动表的隐性问题
• 结构化表格在VBA中对应ListObject对象，如果表格只有表头没有数据行，直接引用DataBodyRange会返回空值报错，需要先判断并新增空行
• 粘贴前建议清空表格原有旧数据，避免新旧数据混杂

修正后可直接复用的代码 #

Sub CopyDataToStructuredTable()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim tbl As ListObject
    Dim lr As Long
    Dim rngSource As Range
    
    ' 配置参数，根据你的实际情况修改
    Set wsSource = ThisWorkbook.Worksheets("output") ' 源数据工作表
    Set wsTarget = ThisWorkbook.Worksheets("目标工作表名") ' 结构化表格所在工作表
    Set tbl = wsTarget.ListObjects("你的结构化表格名") ' 结构化表格名称，可在表格设计选项卡查看
    
    ' 定位源数据区域（排除前3行，从第4行第1列到最后一行第18列）
    lr = wsSource.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ' 所有Cells前加wsSource.限定，和Range父对象保持一致
    Set rngSource = wsSource.Range(wsSource.Cells(4, 1), wsSource.Cells(lr, 18))
    
    ' 清空目标表格原有数据（不需要清空旧数据可注释掉这段）
    If Not tbl.DataBodyRange Is Nothing Then
        tbl.DataBodyRange.Delete
    End If
    
    ' 复制源数据，粘贴值到表格首个数据行
    rngSource.Copy
    tbl.ListRows.Add(1).Range(1).PasteSpecial Paste:=xlPasteValues
    
    ' 清空剪贴板，取消复制状态
    Application.CutCopyMode = False
End Sub

额外注意点 #

• 如果源数据列数和结构化表格列数不一致，粘贴会出现错位，可在复制前加校验：If rngSource.Columns.Count <> tbl.ListColumns.Count Then MsgBox "源数据列数与表格列数不匹配": Exit Sub
• 如果需要保留源数据格式，可把xlPasteValues换成xlPasteValuesAndNumberFormats，不要直接用xlPasteAll，避免覆盖表格预设的公式和样式。

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