Range类Copy方法执行失败：隐藏工作表复制数据报错

问题概述 #

• 执行VBA代码将数据复制到隐藏但未受保护的工作表时，触发错误：Run-time error '1004': Copy method of Range class failed
• 报错行：sourceRange.Copy targetCell
• 异常特征：取消隐藏目标工作表后代码可正常运行，尝试激活目标工作表无法解决问题

错误原因 #

VBA的Range.Copy方法在指定Destination参数时，要求目标工作表必须处于可见状态——即使工作表未受保护，隐藏状态下也会触发该权限类错误。

解决方案 #

方案1：临时切换工作表可见性 #

先取消目标工作表的隐藏状态，完成复制后再恢复隐藏，可搭配屏幕更新关闭避免界面闪烁：

Sub CopyDataToTable()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim sourceRange As Range
    Dim targetCell As Range
    Dim wasHidden As Boolean

    ' 关闭屏幕更新，避免界面闪烁
    Application.ScreenUpdating = False

    ' 定义源表和目标表
    Set sourceSheet = ThisWorkbook.Sheets("Report Data")
    Set targetSheet = ThisWorkbook.Sheets("ReportAsTable")

    ' 记录目标表原本的隐藏状态，临时取消隐藏
    wasHidden = (targetSheet.Visible = xlSheetHidden) Or (targetSheet.Visible = xlSheetVeryHidden)
    If wasHidden Then targetSheet.Visible = xlSheetVisible

    ' 获取源表数据范围（排除表头）
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    lastColumn = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column
    Set sourceRange = sourceSheet.Range(sourceSheet.Cells(2, 1), sourceSheet.Cells(lastRow, lastColumn))

    ' 复制数据到目标表
    Set targetCell = targetSheet.Cells(2, 1)
    sourceRange.Copy targetCell

    ' 恢复目标表的隐藏状态
    If wasHidden Then targetSheet.Visible = xlSheetHidden

    ' 清理剪贴板，恢复屏幕更新
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

方案2：直接赋值替代Copy方法 #

如果仅需要复制单元格的值（或按需复制格式等属性），可以跳过Copy方法，直接赋值——这种方式不受工作表隐藏状态限制：

Sub CopyDataToTable()
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim sourceRange As Range
    Dim targetRange As Range

    ' 定义源表和目标表
    Set sourceSheet = ThisWorkbook.Sheets("Report Data")
    Set targetSheet = ThisWorkbook.Sheets("ReportAsTable")

    ' 获取源表数据范围（排除表头）
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    lastColumn = sourceSheet.Cells(1, sourceSheet.Columns.Count).End(xlToLeft).Column
    Set sourceRange = sourceSheet.Range(sourceSheet.Cells(2, 1), sourceSheet.Cells(lastRow, lastColumn))

    ' 匹配目标区域大小并直接赋值（仅复制值）
    Set targetRange = targetSheet.Cells(2, 1).Resize(sourceRange.Rows.Count, sourceRange.Columns.Count)
    targetRange.Value = sourceRange.Value

    ' 如需复制格式，可添加以下代码：
    ' sourceRange.Copy
    ' targetRange.PasteSpecial Paste:=xlPasteFormats
    ' Application.CutCopyMode = False
End Sub

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