Why must I specify workbook here?

[terrypin]

I'm stepping through very basic VBA to re-learn some essentials.

I have two workbooks open, each with a couple of worksheets. No duplicate names. The two open sheets are called Track Data and TEMP respectively.

Why does this fail with subscript out of range please?

Worksheets("Track Data").Activate

It works if I specify the workbook.

Workbooks("TEST track sheet copying.xlsm").Worksheets("Track Data").Activate

That involves finding its name and carefully pasting it into my code, so I'd prefer not to do so. Why must I specify it in this situation please? In what circumstances can it be left out?

Terry, East Grinstead, UK

[Norie]

Oops, misread code.

[terrypin]

Eh? Once again, I don't follow you! I asked why I have to specify the workbook.

Obviously, as we established in another thread, if the workbook is included, as I did in the second case, then the extension has to be included! As I did.

[xlnitwit]

If Track Data is not in the active workbook, you need to specify the workbook when you refer to it. Also, if the code is in the ThisWorkbook module of a different workbook, you need to specify the correct workbook- either by name or by ActiveWorkbook.

[terrypin]

If Track Data is not in the active workbook, you need to specify the workbook when you refer to it. Also, if the code is in the ThisWorkbook module of a different workbook, you need to specify the correct workbook- either by name or by ActiveWorkbook.

Thanks, understood (I think!). So, would you recommend always specifying the correct workbook as a safe rule?

[xlnitwit]

Yes. Even if you only specify ActiveWorkbook, it's better always to be specific.