Code Works, but only sometimes?

stillenacht

New Member
Joined
Nov 16, 2015
Messages
6
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.
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

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

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top