Active cell contents: Show contents of active cell (any in a particular column) in another cell

[Carlos Ruano]

Hi,

Is it possible to show the contents any single active cell in a particular column, in another specific cell?

For example, let's say that I want to be able to click on any cell in column C, and have the contents of that cell appear in cell K4. I would also want to be able to keep selecting different cells in column C, and for K4 to update each time. Can that be done?

Thank you!
Carlos

 

[Chihiro]

Something like below. Code goes into Worksheet Module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 Then
    [K4].Value = Target.Value
End If
End Sub

 

[Carlos Ruano]

Something like below. Code goes into Worksheet Module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 Then
    [K4].Value = Target.Value
End If
End Sub

Thank you, Chihiro! Your code worked perfectly!

Now, if I may .... I have had some time to think of ways to improve what I am trying to do. Would it be possible to do the same thing that your code does, but with one modification. What I would now like it to do is send the contents of column C to K4 (just as before), but I do not want to be limited to having to click in column C. In other words, for example, I want to be able to select F11, and send the contents of C11 to K4. So, I can select any cell in any row, and the contents of C in that particular row will appear in K4.

Am I asking for too much?

 

[Chihiro]

Try...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
[K4].Value = Cells(Target.Row, 3).Value
End Sub

 

[Carlos Ruano]

Try...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
[K4].Value = Cells(Target.Row, 3).Value
End Sub

Amazing! Haha, well ... to me this is wizardry.

Thank You Thank You Thank You!!!

I have been wanting to learn VBA for some time now, but just haven't had the time nor, honestly, the brain power.

Would you mind explaining what your macro does step by step? I totally understand if you don't do it.

If Target.Count > 1 Then Exit Sub --> I don't understand what is happening here

[K4].Value = Cells(Target.Row, 3).Value --> It looks like you are saying that K4 should equal the cell value of the third column of the row that is selected.


Another question: If I paste your code as you have it, and then paste it again right below that, but change [K4] to [K7], and (Target.Row, 3).Value to (Target.Row, 4).Value, it does not work as I expected. I thought it would put the value of the cell at column D, and the selected row, but it does not. Why not?

I figured out the problem with the above question. I got it to work.

Again, you don't have to explain anything. You've already helped me SO much. Thank you!

 

[Marc L]

Hi !

for example, I want to be able to select F11
and send the contents of C11 to K4.

Checking column of the selection first cell :​

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target(1).Column = 6 Then [K4].Value = Target(1)(1, -2).Value
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Chihiro]

I often have this portion "If Target.Count > 1 ThenExitSub" in selection change event, so that code only proceeds if only 1 cell is selected.

Reason being, I usually don't want the code to proceed when copying range of cells.

 

[Carlos Ruano]

I often have this portion "If Target.Count > 1 ThenExitSub" in selection change event, so that code only proceeds if only 1 cell is selected.

Reason being, I usually don't want the code to proceed when copying range of cells.

Ah, I see, thank you for the explanation.

Now, if I might pester you again

Is it possible to have your code ignore rows 1 through 7? Here is what I mean:

I am using your code to populate cells A2 and A4 (used to be K4 in my above posts). It is working perfectly; however, since your code is written to populate A2, for example, with the contents of the cell in column C for any row that I click in, it is also populating A2 with the contents of the cell in column C for rows 1:7.

The problem with that is that I have a small range of data in A1:E4. This is the section where your code is populating (A2 and A4). Then I also have a larger range of data from A6:AF200. I would like your code to act/function on the lower range of data only (A8:AF200; this is excluding headers). Can that be done?

By the way, is there any way I can do more for you than just "Like" your posts? You have been tremendously helpful to me.

Thank you,
Carlos

 

[Chihiro]

Sorry, I had missed your reply earlier. Can you upload sample workbook with what you are intending to do?