Simple Macro - I think... Copy - Paste on Click

[Scott Campbell]

Excel 2007
I have a workbook with 2 Sheets: Sheet1 and Sheet2

Sheet1 contains a list of all 50 states in column A

Goal: When any item in the cell in the list of 50 states is clicked on
Sheet 1, Cell A1 on Sheet2 will be populated with the contents of the cell
that is clicked.

Any suggestions?
Thanks in advance

 

[Dave]

Hi,
Try this code into sheet1 in the VBA window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Sheet2").Range("A1") = ActiveCell
End Sub

Regards - Dave.

 

[Scott Campbell]

Dave, thanks for this reply. The code works quite well.

I would like to enhance it as follows:

1. Is there a way to limit this to just column A? So if I click somewhere
in column B Sheet2!A1 does not change

2. Limit it to only a double click

3. After clicking, it will jump to cell A1 on Sheet2

Thanks again

 

[Dave]

Hi Scott,
Try this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If ActiveCell.Column = 1 Then
Sheets("Sheet2").Range("A1") = ActiveCell
Sheets("Sheet2").Select
End If
End Sub


You may have to unwrap the first line if it gets wrapped by this window.

Regards - Dave

 

[Scott Campbell]

Thanks Dave. It worked out perfect.