Accessing two different workbooks from personal macro

Question

I have a personal macro which accesses two different workbooks.

Here is the updated code:

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

When I run the code, if A is active, then the MsgBox for A works, but for B I get this error:

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

If B is active, I get the error.
If I comment out all three lines related to A, the MsgBox for B works fine.

Any idea what I can do to access both workbooks?

I do not understand what qualifying a range to a worksheet means. Any suggestions of where I can go to read about this?

Thanks.

There are two "Range"-s, but only one of them is qualified with " Workbooks("A").Worksheets("Sheet1")." Each of the Range-s needs to be properly qualified, A or B. Just because the second one is inside parens does not mean that it inherits the outer qualification. — donPablo, Dec 12 '18 at 16:43
Look at [With...End With](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/with-end-with-statement) to shorten the code - as @donPablo said `Range("A1").End(xlDown)` needs to be qualified to the worksheet to. — Darren Bartrup-Cook, Dec 12 '18 at 16:58
Thanks. I looked up qualifying an object, and tried adding this to my code, along with changing the references, but I still have the problem. : Dim ws1 As Worksheet Set ws1 = Workbooks("Submittals").Worksheets("Sheet1") Dim ws2 As Worksheet Set ws2 = Workbooks("Previous").Worksheets("Sheet1") How do I qualify worksheets from two different workbooks? — H. Watkins, Dec 12 '18 at 17:03
@H.Watkins Please edit your fully updated code - there's a good chance that you're still not fully qualifying all your `Range`s. — dwirony, Dec 12 '18 at 17:44
Unfortunately, I am still confused about how to qualify the range to the worksheet. Any more suggestions? — H. Watkins, Dec 12 '18 at 19:36

score 2 · Answer 1 · answered Dec 12 '18 at 19:48

2

You still had 2 unqualified Ranges in your updated code - I've fully qualified them for you below:

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

answered Dec 12 '18 at 19:48

dwirony

5,487
3
21
43

Thanks so much! I finally understand what everyone was trying to tell me. Blessings! – H. Watkins Dec 12 '18 at 21:24
2

Please note that you cannot use `Integer` for row counting variables. Excel has more rows than `Integer` can handle and `.Count` returns a `Long`! Therefore both variables need to be of type `Long`. • Regardless I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. – Pᴇʜ Dec 13 '18 at 07:23

Accessing two different workbooks from personal macro

1 Answers1