Referencing value from Active cell of another sheet

**hiteshdemla** · 02-24-2013, 01:31 PM

Hi

I want value in Cell "B2" of "Sheet2" and "Sheet 3" to have the value as active cell in "column C" of "Sheet1".

Please help

**dilipandey** · 02-24-2013, 02:08 PM

Hi Hitesh,

Try using below code:-

Please Login or Register  to view this content.

Regards,
DILIPandey
<click on below * if this helps>

**Jakobshavn** · 02-24-2013, 02:27 PM

This will work with a macro-enabled workbook. Install the following event macro in the worksheet code area of Sheet1:

Please Login or Register  to view this content.

Then in any other sheet, enter:

=ActiveC

to display the value in the active cell in Sheet1

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

**hiteshdemla** · 02-24-2013, 02:30 PM

Hi Dilip

Thanx

I believe this is a macro.

Actually I do not want a macro. I want it to be more dynamic.

Such that I select a cell in "Sheet1" and then select "Sheet2" and that should have the value of active cell of sheet1

**hiteshdemla** · 02-24-2013, 02:37 PM

Hi Jakob

Its not working. Should I paste "=ActiveC" in the cell "B2" of "Sheet2"? or I am doing it wrong?

**hiteshdemla** · 02-24-2013, 02:40 PM

I was using this code. It is working good for odd numbered rows and not working for even numbered rows.
Also, this is for one sheet, I want it for 2 sheets
This code was pasted on VBE for Sheet1

Please Login or Register  to view this content.

**Jakobshavn** · 02-24-2013, 02:52 PM

See the attached:

**hiteshdemla** · 02-24-2013, 03:21 PM

Thanx Jakob

Earlier, I didn't named "Column C" Range. Its working good now.

In Cell "B2" of "Sheet2" I actually have a Drop down list for the "Column C" values of "Sheet1". So if I use drop down list. after that it doesn't work as the value gets changer from "=ActiveC" to the value chosen from dropdown list.

**hiteshdemla** · 02-24-2013, 03:27 PM

Ultimately using a mix of codes suggested by Dilip and Jakob.

Used Dilip's macro.

and getting the macro trigger as soon as their is change in slection, as suggested by Jakob

Thanx Guyz.