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
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