RunTime error 1004 when trying to reference a worksheet in a different workbook and assign it to a variable

Question

i'm trying to work with two sheets in two different workbooks. For some reason this code returns an error

Sub look()
Dim Source, Destination As Worksheet
Dim Range1_in_Source As Range
Dim Range1_in_Destination As Range

path = "" :file = "file name"

Set Source = Workbooks(2).Worksheets(1)
Set Destination= ThisWorkbook.Worksheets(1)

this assignment returns an object that is empty for Source when i inspect it using watch Destination however seems to work well -for now!

Set Range1_in_Source= Source.Range(Cells(2, 1), Cells(2, 1).End(xlDown)) 
Set Range1_in_Destination = Destination.Range(Cells(5, 1), Cells(5, 1).End(xlDown))

for some reason the last line gives a runtime 1004 error (method 'Range' of object '_global' failed) And the cells are referring to the Source sheet not Destination.

what is wrong in my code

You have declared Source as Variant. The line should be: Dim Source As Worksheet, Destination As Worksheet. Workbooks(2) is not a good idea it might as well be the same as 'ThisWorkbook'. xlDown is also a bad idea. And Cells are not qualified. Try posting the full code from Sub to Sub. There is an edit button below your post. — VBasic2008
Good point, But even after I declare both as worksheet, I still have the exact same problem.I'm not sure the rest of the code will relate to the problem here but I will — Abdalla Ismail

VBasic2008 VBasic2008 · Accepted Answer · 2020-06-21T20:01:04

Assignment Issues

Use Option Explicit at the beginning of every module.
Avoid using Worksheets(1) and especially Workbooks(2). They have their names, and even better code names.
xlDown is mostly avoided in favor of xlUp.
A common mistake is to forget to qualify Cells, Rows and Columns, and even ranges.
For a better readability, use shorter meaningful names for variables.

Your error occurred because you didn't qualify Cells (e.g. src.Cells, dest.Cells...), so the code tried to 'get' a range (object, not address) on the destination sheet 'using' cells(object, not address) from the source sheet which obviously is not possible.

The Code

Option Explicit

Sub look()
    
    Dim src As Worksheet: Set src = Workbooks(2).Worksheets(1)
    Dim dest As Worksheet: Set dest = ThisWorkbook.Worksheets(1)
    
    Dim rngSrc As Range
    Set rngSrc = src.Range(src.Cells(2, 1), src.Cells(2, 1).End(xlDown))
    Dim rngDest As Range
    Set rngDest = dest.Range(dest.Cells(5, 1), dest.Cells(5, 1).End(xlDown))

    Dim Path As String: Path = ""
    Dim file As String: file = "file name"

End Sub

RunTime error 1004 when trying to reference a worksheet in a different workbook and assign it to a variable

1 Answers

Assignment Issues