VBA - copy & paste from a cell on one worksheet to another worksheet by double clicking

[simo2509]

Hi All,

I have a spreadsheet with two worksheets. what i would like to do is when a cell within column D on worksheet 2 for example is double-clicked on, it will copy the data from that cell into cell B2 on worksheet 1.

Likewise, if possible, I would like to make it so that double clicking on a different cell in the same column will automatically replace the data that is in B2 on WS1, rather than having to physically delete the data in cell B2 on WS1 before being able to add other data.

I cant see any other way of doing this without VBA, which the only experience of using it I have, is that I have used it in this sentence.

Any sort of help on this would be greatly appreciated.

Cheers,

Liam

 

[wideboydixon]

Right click on the Sheet2 tab and select "View Code". Paste this in there:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Target.Count = 1 And Target.Column = 4 Then
    Sheets("Sheet1").Range("B2").Value = Target.Value
    Cancel = True
End If

End Sub

WBD

 

[simo2509]

That's amazing, worked a treat! thanks for your help!