Help with Reslving a VBA Code Execution Error

Hello,

I've written a VBA macro that allows me to perform an automatic data retrieval on daily basis from a PI Server using Excel's add-on "PI DataLink". Within my spreadsheet, certain columns get populated with arrays from PI DataLink while others are formulas that simply need to be copied down daily as new PI data gets retrieved into the spreadsheet. However, when I execute the code, I receive the following error: "Application-defined or object-defined error" after the code activates my "Prod Allocation" worksheet and attempts to select a certain row. The error occurs at the following command: Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Prod Allocation").Range(Cells(i - 2, 8), Cells(i - 2, 122)).Select

I've checked the code and verified the worksheet name dozens of times to ensure there is not a spelling error, and I'm unable to figure out why the code fails on selecting a certain line. Could you please help me resolve this issue? The code is shown in full below. Not sure if this is relevant, but all coding was done in Excel 2010.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Please Login or Register  to view this content.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Does your error appear right at the first iteration? Or is i already a higher number?
Odd nothing seems wrong, and it should not be a typo on sheet name, as you have activated that sheet already by the time you go to select.

Question, is your "Prod Allocation" sheet hidden? That allows the activate command I'm pretty sure, but you can't select .... it would generate that error.

Where is this code located?

Yegor, it should be in a module, not sheet.

You need to make sure every instance of Cells and Range has a worksheet reference.

For example this,

Please Login or Register  to view this content.

should be something like this.

Please Login or Register  to view this content.

If you don't have worksheet references then the code is going to error as VBA will be interpreting Cells/Range to be referring to either the active worksheet, or, if the code is in a sheet module, the sheet whose module the code is in.