Restricting macro to one workbook

[Wyndon]

I have a spreadsheet containing a macro that works fine when used in conjunction with Guardian to automate betting. The problem comes when the day job occasionally intervenes and I have to open another excel spreadsheet. The macro crashes with the message "Run Time error 9 - Subscript out of range". I've done obvious things like making worksheet names unique to each workbooks - to no effect. Is there a way to make a macro specific to one workbook, so that opening others does not affect its operation? I've tried to google for an answer, but found the suggestions confusing.

[nigelk]

Put something like this at the beginning of your subroutine then specify what book you are referring to as well as the sheet name

Sub TEST()

Dim Mybook As Workbook

Set Mybook = ThisWorkbook

Mybook.Sheets("sheet1").Range("a1") = Mybook.Name

End Sub

[Wyndon]

Thanks very much Nigel, I'll give it a go. What is Mybook.Sheets("sheet1").Range("a1") = Mybook.Name doing? Does it mean that everytime I refer to a sheet, I also link it with Mybook?

[Fidib665]

What works for me is opening another instance of guardian (with a connection rate of 1, so it doesn't hamper too much with my overall connection) and then opening any new workbook from that instance of guardian.
Cheers, F

[Wyndon]

Thanks Fidib. The new workbook that is interfering is unconnected with my betting activities, so I wouldn't use Guardian to open it. I think nigelk's answer might do the trick, but would appreciate a bit of narrative, as my vba knowledge is quite limited. I'm not sure for instance which are generic commands/functions and where I should be using my own workbook/worksheet names.

[nigelk]

Hi Wyndon

All I'm doing is identifying which workbook I am refering to:

Set MyBook=Thisworkbook just gives a name to the workbook I want to use. You can call it anything you want.(BetAngelBook would be just as valid).

If I have two or three workbooks open, I need to identify the one I mean so I would write something like:

Mybook.Sheets("sheet1").Range("a1")...............

It will now just look at the workbook I want (Mybook),sheet1, range A1

You are on the right track, excel is just a bit confused as to which book you are referring so just make it crystal clear and always use full references

Mybook.Sheets("sheet1").Range("a1")...............

rather than just

Sheets("sheet1").Range("a1")...............

[Wyndon]

Many thanks for confirming that I was on the right track Nigel - it made me persevere. I think I may have finally got there after much trial and error. It was complicated by the fact that I adapted someone else's macro for my own use and so I did not always understand what the coding was doing. Also on some occasions I wanted 2 instances of BetAngel running at the same time - which created new problems. But I've learned a bit about macros and debugging, which should prove invaluable in the future. I had to keep telling myself that computers are logical and if you follow the logic of what is goin on you get there in the end. Thanks again.