Code Works, but only sometimes?

[stillenacht]

Hey all, for some reason my VBA code sometimes works, but sometimes spits out "Run-time error '1004': Application-defined or object-defined error".

The section that causes trouble is always this one, specifically the select line:


'Entering Stocks from IB Report


Dim Report As Workbook
Set Report = Application.Workbooks("IB_statement.csv")
Report.Sheets("IB_statement").Range(Range("D29"), Range("D29").End(xlDown)).Select
Selection.Copy
Analysis.Sheets("Profit Calculations").Range("C3").PasteSpecial

The rest of the code is copying things from xlsx files, so I wonder if that's causing the issue. On more testing, it looks like it runs fine if i have the IB_statement file open AND selected.

 

[Jonmo1]

When nesting ranges, and you specify the book/sheet on the outside range, you must also specify it on the inside ranges too.
Also, you can't select a sheet that is not currently active. (you don't need to select the range anyway.

Try

Dim Report As Workbook
Set Report = Application.Workbooks("IB_statement.csv")
Report.Sheets("IB_statement").Range(Report.Sheets("IB_statement").Range("D29"), Report.Sheets("IB_statement").Range("D29").End(xlDown)).Copy
Analysis.Sheets("Profit Calculations").Range("C3").PasteSpecial

That can be simplified with a WITH structure

Dim Report As Workbook
Set Report = Application.Workbooks("IB_statement.csv")
With Report.Sheets("IB_statement")
    .Range(.Range("D29"), .Range("D29").End(xlDown)).Copy
End With
Analysis.Sheets("Profit Calculations").Range("C3").PasteSpecial

 

[stillenacht]

Ah that makes total sense! Thank you, this has been causing me difficulties.

 

[Jonmo1]

You're welcome.