VBA macro in read-only workbook

[JC3]

I've written/recorded a macro that copies certain sheets from one workbook to another. It works fine when I am the only person in the workbook or if I select to open it read only, but if there is someone else in the workbook when I open it then it fails.
Trying to run the macro produces this error message:

"Run-time error '1004': Select method of Sheets class failed"

on this line:

"Sheets(Array(4, 6, 9, 10)).Select"

I have found a work-around by making the whole workbook read-only but I'd love to know why it does this.

Can anyone put me out of my misery?

[Damon Ostrander]

Hi JC3,

I'm only guessing here, but I think it is a fairly educated one. When you select multiple sheets in a shared workbook, you are doing a fairly dangerous thing, because this implies that any change made to any of the selected sheets gets made to all of them. Another way of looking at this is that the active cell on the active worksheet becomes active on all the selected sheets, which means they are ready for data entry. Thus the select operation requires that all the sheets you select be locked so that you are the only one that can change them. But what if someone else is currently working on one of these sheets, and therefore already has it locked?

It may be that because of this potential for problems, Excel does not allow multi-sheet selections in shared workbooks. Or, it could be that it just runs into a lock conflict if you and another user both try to lock the same sheet or cells at the same time. Either way, you get the same result.