Get Return Value Of Active Cell On Change Of Selection Change

[Safal Shrestha]

I was wondering if there is any possibility of a macro, which returns to a particular cell, the content of the active cell.
And the active cell can be any where in the same workbook except the cell in which the result is shown.
Like,

A1 is the cell where I need the content of the active cell. and this active cell can be any where except A1

Regards,
Safal

[Gary's Student]

Install the following Event macro in the worksheet code area:



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = ActiveCell.Text
End Sub



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:

Getting Started with Macros and User Defined Functions (http://www.mvps.org/dmcritchie/excel/getstarted.htm)

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

Event Macros, Worksheet Events and Workbook Events (http://www.mvps.org/dmcritchie/excel/event.htm)

Macros must be enabled for this to work!

[Safal Shrestha]

Thanx. It works great.

[Safal Shrestha]

It is working fine with the single sheet but how can i make it work in the entire workbook? Can I do make it work in every open workbook?

[Gary's Student]

You would need to replicate the code on every sheet of every open workbook.

[Safal Shrestha]

Do I have to do it for different sheets in the same workbook?

[Gary's Student]

I believe that is correct.

[LalitPandey87]

You can try this and no need to replicate the code just follow the steps:


Open excel
Press Alt + F11
In the right side you can see Project explore if not press Ctrl + R
Double click on ThisWorkbook
Paste below code



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Range("a1").Value = ActiveCell.Value
End Sub

:cheers:

[Safal Shrestha]

Thanks. Also I used the sheet name so that I can get the desired result, whereever the active cell is cell A1 of sheet1 will show the content.