VBA copy value of selected cell in specific column

Question

I've been looking for a way to code in VBA: when I click on a certain cell in range X10:X5000, Excel should copy that cell value to a specific cell B10.

Currently I have the following, but it only works when the cell I select is an actual value. When the cell I select is the outcome of a formula, the target cell shows the value for a fraction of a second and then the cell becomes blank.

So how can I make sure I always copy the value of the selected cell and not the formula? In the example, I did not take into account the range, but only a selected column.

Thanks for the help!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 19 Then ' check if cell modifed is in column "S"
Target.Copy Range("B10")
End If

End Sub

Thanks!

danl danl · Accepted Answer · 2017-06-16T08:50:00

Try using

Range("B10").Value = Target.Value

instead of

Target.Copy Range("B10")

This should transfer the value across, instead of the formula

VBA copy value of selected cell in specific column

1 Answers