1

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

braX
  • 11,506
  • 5
  • 20
  • 33
Abdalla Ismail
  • 45
  • 4
  • 1
    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 Jun 21 '20 at 19:42
  • 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 Jun 21 '20 at 19:46

1 Answers1

1

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thank you very much qualifying cells helped in my case. I have further questions if you have time to reply i'd be grateful. I don't really get why not using option explicit is bad, if you have a link explaining that it'd be nice. I'm not sure how i can use xlUp if i don't know the last row? or am i missing something here? – Abdalla Ismail Jun 21 '20 at 21:25
  • 1
    `Option Explicit` forces you to declare all variables which helps against unexpected errors and helps you to faster learn and better understand the language. In your case, `xlUp` can be utilized like this: `Set rngDest = dest.Range(dest.Cells(5, 1), dest.Cells(dest.Rows.Count, 1).End(xlUp))`. – VBasic2008 Jun 21 '20 at 21:39