In avoiding .Select, Declared Range variable works on the activesheet but [M5] works on all sheets. Why?

Question

I've been working through some of the ideas in How To Avoid Using Select in Excel VBA Macros. I have 3 sheets in my test workbook and want to move the cursor to a specific cell on each sheet after other actions have been carried out. The following enters the cell value but only moves the activecell to M5 on the initial sheet:

Dim rngTest As Range
Dim shtTest As Worksheet

Set rngTest = [M5]

For Each shtTest In ThisWorkbook.Sheets
    With shtTest
        .Range("m10").Value = "hello"
    End With
    rngTest.Select
Next shtTest

Where as this does what I want (enters the cell value then move to M5 on each sheet):

Dim rngTest As Range
Dim shtTest As Worksheet

Set rngTest = [M5]

For Each shtTest In ThisWorkbook.Sheets
    With shtTest
        .Range("m10").Value = "hello"
    End With
    [M5].Select
Next shtTest

In the top sample I tried making each sheet active first before moving to rngTest but that created the 1004 error.

So, why does [M5].select work whereas rngTest.Select fails?

`Set rngTest = [M5]` because you qualified `M5` to the active sheet at the time of that line. Edit: you want `Set rngTest = shtTest.range("M5")` inside the loop. — findwindow, Jun 08 '16 at 15:21
Ah ok. I wasn't aware that Active Sheet was assumed like that. I tried doing your suggestion of setting `rngTest` inside the loop but neglected to use the `shtTest` so I will try that. — nathanjw, Jun 08 '16 at 15:30
As a follow-up to what @findwindow said I would like to add that your first code will work if you `Activate` the sheet first: `shtTest .Activate` or `rngTest.Parent.Activate`. You can only select a cell on the currently active sheet. — Ralph, Jun 08 '16 at 15:30
@ralph that gives me the same 1004 error once it reaches `rngTest.Select` — nathanjw, Jun 08 '16 at 15:35
Your second code isn't any different to the first. It would only select M5 on each sheet if the code activated each sheet. — Rory, Jun 08 '16 at 15:38
@Rory you are quite correct. I could swear when I was trying it before creating this post it worked. — nathanjw, Jun 08 '16 at 15:50
Perhaps you had selected multiple sheets inadvertently before running it? Or you had run it on each sheet so M5 was already selected? ;) — Rory, Jun 08 '16 at 15:51

Ralph · Accepted Answer · 2016-06-08T15:43:22.090

Just for clarification:

Dim rngTest As Range
Dim shtTest As Worksheet

For Each shtTest In ThisWorkbook.Sheets
    With shtTest
        .Range("m10").Value = "hello"
    End With
    shtTest.Activate
    Set rngTest = [M5]
    rngTest.Select
Next shtTest

Yet, I'd recommend not to use [M5] to refer to a particular cell / range. I would also prefer to use Range("M5") or Cells(5, "M") or Cells(5, 13) instead.

In avoiding .Select, Declared Range variable works on the activesheet but [M5] works on all sheets. Why?

1 Answers1