Solved: VBAProject (PERSONAL.XLS) Stays Open Forever?

[sam314159]

I've been running a macro for the last year or so that has the following code in it:

Workbooks(1).Sheets(3).Activate

This has been working perfectly until now. I am getting a "Subscript Out Of Range" error and when I put a watch on Workbooks(1) I found out that it is now "PERSONAL.XLS" which is not even open. Workbooks(1) has always been the first workbook I opened and now it's not.

I don't want to hard code the name of the workbook because it can sometimes change. Everything works out fine if I change the reference to Workbooks(2) but no one else is having that problem but me and we are sharing this macro.

Any ideas on how to fix this?

[mdmackillop]

Personal.xls is usually open but hidden. You could try ActiveWorkbook instead of Workbooks(1)

[GTO]

As I have never used/created a personal.xls (save maybe while reading a thread here), just a guess, but might have you inadvertantly created personal.xls while recording a macro?

[Paul_Hossler]

If you don't want a Personal.XLS you can just delete it. It's usually in XLSTART

Otherwise, if the macro code is in the workbook you want to operate on, I'd use ThisWorkbook.Sheets(3). .....

ActiveWorkbook is the WS that is currently active, and MIGHT not be the WB that the macro resides in

So, if this were a oft used macro, you could put in Personal.xls, use ActiveWorkbook to refer to the one that you want to run the macro on. Where necessary, use ThisWorkbook to refer to the WB that the macro is in

Paul

[sam314159]

I'd use ThisWorkbook.Sheets(3)

That did the trick, thanks for all the replies guys.