Selecting all sheets using personal.xlsb

[barbs706]

Hi all.

I am trying to get a script together that will select all sheets (not hidden ones) on opening of a workbook.

We have a vast amount of workbooks that date back to 2006 so I was hoping I could use the PERSONAL.XLSB instead of manually adding code to every workbook.

A lot of the workbooks have differently named sheets so the code needs to be universal so that it works regardless of sheet name/quantity.

I have managed to get the PERSONAL.XLSB to open automatically with one of our workbooks, by recording a dummy macro (personal.xlsb was missing) but now I am at a loss with what to do next!

Can anyone suggest the code? Thank you!

 

[Fishboy]

I have just setup 'ThisWorkbook' as you described, but I am getting the following error:

Run-time Error '9'
Subscript Out Of Range

Debug:
' Select all sheets in ArraySheets
Sheets(ArraySheets).Select

It is defiantly opening up the workbook now through, as the filename now appears in the top window frame.

Strange how we have two different error codes, but still failing at the same place?

That would be great if you could start your own thread. Let me know if you find any more info or would like me to try some things. I will be monitoring this thread like a hawk!

I've started my own thread for this issue here:

http://www.mrexcel.com/forum/excel-...orks-standalone-but-breaks-personal-xlsb.html

 

[barbs706]

Excellent!
Thanks Fishboy