WHY Activate a Worksheet?

You hardly ever need to activate a worksheet (or select any cells etc)!
You can copy, paste, delete columns without activating any worksheets
You can attribute values without activating
And you can do all of that within one worksheet, within one workbook , across multiple workbooks

Put both attached files in the same folder
Open File002 and look at the value of cell A1
Now close it
Open File001 and run macro below with {CTRL}K
Open file002 and check value in A1
Nothing has been activated in the code but the value in A1 has changed

Please Login or Register  to view this content.

Whatever preconceptions you have, ditch them and start again with an open mind

The problem is you work from a lot assumptions of how you think it works and not follow the vba logic..
in your code you reference "othersheet.[othercell] to us in words you tell that othersheet refers to a different workbook.
So tell in your code it is in another workbook..

if you want to reference another workbook you need something like

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

most likely you also want to check if that other workbook is open to avoid code failing if you forgot to open

Originally Posted by Merf

Or to put it from another perspective, an unqualified range name reference is assumed to refer to ActiveWorkbook, NOT ThisWorkbook?

That is correct, assuming the code is not in an object module that has a Range property. Your code is really calling Evaluate rather than Range, but the same principle applies.

Beware of this twist

STANDARD module vs SHEET module
If the code is in a standard module - unqualified ranges refer to the active sheet
If the code is in a specified sheet module - unqualified ranges refer to the specified sheet

Example
One workbook, 2 sheets ("Data" and "Values") and sheet "Values" is the current ACTIVE sheet


If the code is in a STANDARD module
...Range("A10") refers to sheet "Values" (ie the active sheet)

If the code is in the SHEET module for sheet "Data"
...Range("A10") refers to sheet "Data" (even though "Values" is active)

If the code is in the SHEET module for sheet "Values"
...Range("A10") refers to sheet "Values" (but not because it is the active sheet)

Could the problem be the use of the []?

Originally Posted by kev_

If the code is in a standard module - unqualified ranges refer to the active sheet

Unless it's a named range, in which case it refers to the active workbook. In a worksheet code module, it's effectively Worksheet.Range whereas in any other module, it's Application.Range.