VBA跨工作表用Range(Cells...)引用可变范围值遇1004错误求助

核心错误原因 #

你遇到的1004错误，本质是未给Cells对象显式指定父工作表。当你编写Worksheets("Staff list").Range(Cells(2, "A"), Cells(22, "A"))时，Cells(2, "A")默认指向当前活动工作表的单元格，而非Staff list工作表的单元格。Range对象要求它的两个边界单元格必须属于同一个父工作表，这种跨工作表的Cells引用就会触发错误。

逐个分析你的代码 #

正常运行的代码 #

Range(Cells(2, "A"), Cells(ListSize, "A")).Value = Worksheets("Staff list").Range("A2:A22").Value

右侧用字符串地址"A2:A22"定义范围，明确隶属于Staff list工作表，和父对象匹配；左侧的Cells默认指向当前工作表，和左侧Range的父对象一致，因此能正常运行。

填充单一值的代码 #

Range(Cells(2, "A"), Cells(ListSize, "A")).Value = Worksheets("Staff list").Cells(2, "A").Value

右侧是单个单元格的数值，VBA会自动将这个单一值批量填充到左侧的整个范围中，这是VBA的默认行为，并非代码错误。

报错的两段代码 #

两段代码的问题完全一致：Worksheets("Staff list").Range(...)内部的Cells没有指定父工作表，导致Cells指向当前活动表，和Staff list表不属于同一个父对象，违反了Range对象的参数要求，因此触发1004错误。

正确的写法 #

要解决这个问题，只需给Cells显式指定父工作表，以下是两种常用方案：

方案1：用With语句简化代码 #

Dim staffSheet As Worksheet
Set staffSheet = ThisWorkbook.Worksheets("Staff list")

With staffSheet
    ' 若目标范围在当前活动表，可保留左侧默认；若要更严谨，也可给左侧指定父表
    Range(Cells(2, "A"), Cells(ListSize, "A")).Value = .Range(.Cells(2, "A"), .Cells(ListSize, "A")).Value
End With

With块中的.代表引用staffSheet，确保.Cells和.Range都隶属于同一个工作表，避免跨对象引用冲突。

方案2：直接指定父对象 #

Dim staffSheet As Worksheet
Set staffSheet = ThisWorkbook.Worksheets("Staff list")

Range(Cells(2, "A"), Cells(ListSize, "A")).Value = staffSheet.Range(staffSheet.Cells(2, "A"), staffSheet.Cells(ListSize, "A")).Value

直接给每个Cells指定父工作表staffSheet，确保Range的边界单元格归属一致。

更严谨的写法（推荐） #

为了避免当前活动表切换导致的潜在问题，建议给左侧的Range和Cells也显式指定父工作表：

Dim targetSheet As Worksheet
Set targetSheet = ThisWorkbook.Worksheets("你的目标工作表名称") '替换为实际表名
Dim staffSheet As Worksheet
Set staffSheet = ThisWorkbook.Worksheets("Staff list")

targetSheet.Range(targetSheet.Cells(2, "A"), targetSheet.Cells(ListSize, "A")).Value = staffSheet.Range(staffSheet.Cells(2, "A"), staffSheet.Cells(ListSize, "A")).Value

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