VBA跨同一工作簿多工作表操作Range时报错1004问题咨询

问题成因 #

你代码里未给嵌套的Range("A1")显式指定所属工作表，VBA默认会调用当前激活的工作表的Range对象，而非你前缀声明的ws1/ws2。
以Set rng2 = ws2.Range("A1", Range("A1").End(xlDown))这一行为例：

• 第一个参数"A1"属于ws2
• 第二个参数里的Range("A1")默认属于当前激活的工作表，如果此时激活的不是Sheet2，两个边界单元格归属不同工作表，就会触发_worksheet对象的Range方法调用失败的1004报错。

解决方法 #

所有Range对象调用都显式绑定所属工作表，修正后代码如下：

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim rng1 As Range, rng2 As Range

Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")

' 两处嵌套Range都显式指定所属工作表为ws1
Set rng1 = ws1.Range("A1", ws1.Range("A1").End(xlDown))
' 两处嵌套Range都显式指定所属工作表为ws2
Set rng2 = ws2.Range("A1", ws2.Range("A1").End(xlDown))

如果想简化写法也可以用With块减少重复输入：

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range, rng2 As Range

Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")

With ws1
    Set rng1 = .Range("A1", .Range("A1").End(xlDown))
End With

With ws2
    Set rng2 = .Range("A1", .Range("A1").End(xlDown))
End With

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