Need Activate Sheet first in order to define range ?

**cladelpino** · 03-03-2011, 01:06 PM

Hello, this is my first post (i'm hoping of many) in this forums. I've been using VBA en excel for a while now, but, some, let's say, "advanced fundamentals" (as strange as that sounds) escape me.

I'm currently experiencing one of those situations, and I'd like, if there's any expert around, to explain me the real difference between:

Please Login or Register  to view this content.

which works, and ...

Please Login or Register  to view this content.

which doesn't work.

I've searched around in the usual places, and this forum, to no avail.

Thanks in advance for any help. This matter intrigues me...

Regards
Claudio

**StephenR** · 03-03-2011, 01:08 PM

Every time you have a reference to a Range or Cells you need to qualify it with a sheet reference, otherwise Excel will assume it relates to the active sheet when the macro is run. Your first extract gets round this by activating the sheet first so further references are not needed. In your second, you are partly referencing Reconciliated... and partly the active sheet which causes an error. A better formulation would be:

Please Login or Register  to view this content.

**cladelpino** · 03-03-2011, 01:13 PM

Stephen:

Thank you very much for your help. Yet, your answer brings forth some more questions

Why isn't the Sheets("foo") a sheet reference good enough ?

Why is the active sheet still referenced ?

and why does "With" turn "Sheets("foo")" into a valid sheet reference, ?

Thanks again!!!

Regards
Claudio

**StephenR** · 03-03-2011, 01:17 PM

Why isn't the Sheets("foo") a sheet reference good enough ?

It has to be in front of every Range or Cells, not just the first.

Why is the active sheet still referenced ?

Not sure what this means. Do you mean the second code doesn't work when the Recon sheet is active?

why does "With" turn "Sheets("foo")" into a valid sheet reference

Note the dots in front of every Range and Cells - this does what I said in the first point above. Look up With in Excel Help, it is a very useful construct.

**cladelpino** · 03-03-2011, 01:27 PM

Thanks! I now understand!

I was foolishly misleaded by an MSDN page to assume that the Cells() calls inside the Range() statement need not be sheet referenced.

Thanks again, I will mark it solved.