Workbooks, subscript out of range




I have a delicate and (in my world) irrational problem. I hope you can helt
me understand the situation.

I recently changed from Office 2003 (XP) to Office 2007 (Windows Vista). I
therefore have Excel 2003 workbooks and Office 2007 (.xlsx, .xlsm) workbooks.

Now my little problem:

For a long time I have used the simple command:

Workbooks("book1.xls").Activate or

and it always worked out fine. But, now in my new Office 2007 workbooks this
wont work anymore (Error 9, subscript out of range). But, if I open my old
2003 Excel Workbooks with the same command the code works just fine.

I followed all the threads that discusses this matter and I have tried
everything, such as:

1. Change "book1.xls" to "book1" to "book.xlsx" etc.
2. Changed my windows settings to show/not show .xls for known file types
3. Using a short VBA code that shows the name of the workbook in a message
box just to make sure I have used the right name
4. Used a saved workbook and unsaved workbook
5. Used different computers with Office 2007 with the same error (Error 9,
subscript out of range)

The Workbook that the code suppose to activate will always be open before
the user run the macro.

As I undestand it it has to be a setting question or a reference question.
Or what do you think? It looks so easy, what am I missing???

Thank you for your time and help!!



Hi again!

Some times it helps just to post a qestion here...

My bad, I missed to activate the right worksheet. In Office 2003 I had the
setting of just one worksheet in a new workbook and in Office 2007 it is 3 by

See you next time! :)


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question