Select range in a particular sheet in excel vba

Question

I have a question about selecting a range in a particular sheet using excel vba.

I don't know why the following is not working:

Thisworkbook.Sheets("N&A").Range("B4:F16").select

However this works:

Thisworkbook.Sheets("N&A").Activate
ActiveSheet.Range("B4:F16").Select

The VBA code is programmed on "N&A" sheet.

Could anyone let me know what could be the reason?

Thank you!

Answer 1

You've basically answered your own question. Here's an excerpt from Excel 2003 help:

"If you use the Select method to select cells, be aware that Select works only on the active worksheet. If you run your Sub procedure from the module, the Select method will fail unless your procedure activates the worksheet before using the Select method on a range of cells."

More importantly, remember that it's rarely necessary to use Select in VBA, and it should be avoided if possible.