excel - 从个人宏访问两个不同的工作簿

Question

我有一个访问两个不同工作簿的个人宏。

这是更新的代码:

Sub Copy_and_Paste()
    Dim ws1 As Worksheet Set ws1 = Workbooks("Submittals").Worksheets("Sheet1") 
    Dim ws2 As Worksheet Set ws2 = Workbooks("Previous").Worksheets("Sheet1") 
    Dim num_rows_A As Integer
    Dim num_rows_B As Integer

    num_rows = ws1.Range("A1", Range("A1").End(xlDown)).Count

    MsgBox ("Num rows in A = " & num_rows)

    num_rows_B = ws2.Range("A1", Range("A1").End(xlDown)).Count

    MsgBox ("Num rows in B = " & num_rows_B)
End Sub

当我运行代码时，如果 A 处于事件状态，则 A 的 MsgBox 有效，但 B 出现此错误:

Run time error '1004' Application-defined or object-defined error.

如果 B 处于事件状态，我会收到错误消息。

如果我注释掉与 A 相关的所有三行，B 的 MsgBox 就可以正常工作。

知道我可以做些什么来访问这两个工作簿吗？

我不明白将范围限定为工作表意味着什么。关于我可以去哪里阅读有关此内容的任何建议？

谢谢。

Answer 1

你还有2个不合格Range s 在您更新的代码中-我在下面为您完全限定了它们:

Sub Copy_and_Paste()

    Dim ws1 As Worksheet: Set ws1 = Workbooks("Submittals").Worksheets("Sheet1")
    Dim ws2 As Worksheet: Set ws2 = Workbooks("Previous").Worksheets("Sheet1")
    Dim num_rows_A As Integer
    Dim num_rows_B As Integer

    num_rows = ws1.Range("A1", ws1.Range("A1").End(xlDown)).Count

    MsgBox ("Num rows in A = " & num_rows)

    num_rows_B = ws2.Range("A1", ws2.Range("A1").End(xlDown)).Count

    MsgBox ("Num rows in B = " & num_rows_B)

End Sub