1004 runtime error when using row count in opened file

[lestersk]

Would someone please explain why I get the error. in Excel 2013

Run-time error '1004':
Application-defined or object defined error

Senario (Description followed by code)
I have opened a workbook and worksheet (wsBS) from within the main worksheet (wsData).
I want to find the last row of the main worksheet and the last row of the opened worksheet.

LastRow and lastRowBS are defined as Integers

The following code works for the main worksheet (wsData)

LastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row

The following code for the opened worksheet fails

lastRowBS = wsBS.Cells(Rows.Count, 1).End(xlUp).Row

However the following code for the opened worksheet works (workaround)

lastRowBS = wsBS.Range("A65536").End(xlUp).Row

can anyone please explain why?

Thank you

 

[My Aswer Is This]

Do not understand this:
I have opened a workbook and worksheet (wsBS) from within the main worksheet (wsData).

What do you mean by "main worksheet"

Do you mean active worksheet.
And what is "wsBS"

I think your saying worksheet when you mean Workbook

 

[Fluff]

Is the opened workbook an xls file?

 

[ScottR]

You haven't provided enough information or sample code to be sure but it's likely to be because the workbook you are dynamically opening is an older file version (xls) and does not have as many rows as the original worksheet. Because you have not qualified the range in Rows.Count it is attempting to start in row 1 million plus (the number of rows in the original worksheet) instead of the maximum 65536 of the opened workbook.

This

lastRowBS = wsBS.Cells([COLOR=#b22222]wsBS.[/COLOR]Rows.Count, 1).End(xlUp).Row

Should fix it (though untested).

 

[lestersk]

ScottR you are an absolute genius. Apologies for not providing enough information however from the vestigial quantity of code I provided, you have diagnosed and resolved the issue. wsBS is indeed a worksheet in a workbook of type .xls . Thank you very much.

 

[lestersk]

Thank you for your response however ScottR has resolved the issue. Apologies for the lack of information however my terminology was correct.

 

[lestersk]

Yes and that was what was causing the issue. You pointed me in the right direction and ScottR has resolved the issue.

 

[ScottR]

ScottR you are an absolute genius.

Two of us now agree. The movement is growing!

Seriously though, glad to be of assistance (especially as you posted with code tags) but one wrapping up post to confirm the issue was sorted probably would have covered it!