Referencing Active Cell's Value from Another Sheet

Right-click on the Period7 sheet tab, select view code, and copy this code:

Please Login or Register  to view this content.

This will run by either clicking on an odd cell in column A, or moving the cursor there with the arrow keys.

HTH

Jason

Hi Jason,

Thanks, but your code is not firing. There was already a worksheet change event...

Please Login or Register  to view this content.

The SelectionChange event is different than the Change event. The Change event is triggered when the range you set is "changed". The SelectionChange event is triggered when you "select" the range.

In your original post when you say "click in a cell in col A", do you simply mean select a cell in col A?

Yes, I tried the code block as is and it did not appear to work.

Col A contains a formula that combines students' last and first names, in cols B and C. Then, this col A value is what I'm using in a VLOOKUP in the report sheet.

It would be great if a teacher could simply click on any odd cell in col A, causing the value in that active cell to be put into the C11 cell in the report sheet.

Thanks again.

Well, the code I posted above works exactly how you are describing. I'm not sure why it is not working on your end.

Try this:

-Go to the VBE (ALT + F11)
-Go to the Immediate Window (CTRL + g)
-Type Application.EnableEvents=True and hit Enter

Try selecting an odd row in column A again, and see if it works.

How cool is that?! It works after doing what you indicated. Must this application events be enabled/disabled in the code, or should it be fine? Thanks.

Based on the way it is written, it should be fine. What probably happened is at one point, the code must have broke before enabling events again.

Thanks again JasonCW

No problem.

HI Jason,

Using same code.

its working for odd numbered rows not for even numbered.

Also since do not want to activate the sheet in which value is copied I've deleted .Activate from last line of code