Hi,

I'm trying to write a spreadsheet (book1) with an onOpen macro that pulls in
project "data" (from book2) to a hidden worksheet 'data' on book1. I've been
able to write the macro with few problems (and a little help) but when I hide
the 'data' worksheet on book1, then open book1 (to run the macro) the macro
doesn't find the last row correctly of a particular dataset.

If I run the macro normally (when i have the document open, and the data
sheet unhidden) it works fine - it's able to locate the last row and work out
the dynamic range fine.

This is the code I'm running:
lastRow = Range("A65536").End(xlUp).Row
ThisWorkbook.Names.Add "Results", datasheet.Range(Cells(2, 1),
Cells(lastRow, 1)), True

I've also tried to set the 'data' worksheet to be visible when the macro
starts (and then re-hidden when it ends) however the macro bombs out with
error 1004 and this message:
Method 'Range' of '_Worksheet' failed

I think this is because the lastRow has been set to 2 (so excel can't work
out the range) and from what I can work out it's because the 'data' worksheet
is hidden from view when the macro starts. Just to clarify, if I stop the
macro, and run it whilst the data sheet is visible it works, it's only when
it's hidden (and made visible in the macro) that this error crops up.

Does anyone have any ideas of what might be happening? I can post more
source code if needs be.

Ah rats - if anyone reads this now I'm going to look silly.

If I "datasheet.Activate" after making it visible, it all works fine :-)

Should have realised this, but glad I've worked it out. Thanks for all the
help that was going to point out the obvious to me :-)

"George" wrote:

Hi,

I'm trying to write a spreadsheet (book1) with an onOpen macro that pulls in
project "data" (from book2) to a hidden worksheet 'data' on book1. I've been
able to write the macro with few problems (and a little help) but when I hide
the 'data' worksheet on book1, then open book1 (to run the macro) the macro
doesn't find the last row correctly of a particular dataset.

If I run the macro normally (when i have the document open, and the data
sheet unhidden) it works fine - it's able to locate the last row and work out
the dynamic range fine.

This is the code I'm running:
lastRow = Range("A65536").End(xlUp).Row
ThisWorkbook.Names.Add "Results", datasheet.Range(Cells(2, 1),
Cells(lastRow, 1)), True

I've also tried to set the 'data' worksheet to be visible when the macro
starts (and then re-hidden when it ends) however the macro bombs out with
error 1004 and this message:
Method 'Range' of '_Worksheet' failed

I think this is because the lastRow has been set to 2 (so excel can't work
out the range) and from what I can work out it's because the 'data' worksheet
is hidden from view when the macro starts. Just to clarify, if I stop the
macro, and run it whilst the data sheet is visible it works, it's only when
it's hidden (and made visible in the macro) that this error crops up.

Does anyone have any ideas of what might be happening? I can post more
source code if needs be.