CODEWITHSUNDEEP
excelvba
H. Watkins
H. Watkins

Reputation: 11

Accessing two different workbooks from personal macro

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.

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.

Upvotes: 1

Views: 54

Answers (1)

dwirony
dwirony

Reputation: 5450

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

Upvotes: 2

Related Questions