Macro running against hidden worksheet
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. |
Macro running against hidden worksheet
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. |
All times are GMT +1. The time now is 03:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com