Click on a Cell and Contents of cell populate another cell in another worksheet

[brumby]

Hi There,

Hopefully an easy one.

I have a worksheet called "Main Sheet", I would like for any cell I click on in Col D, the contents of that cell to appear in worksheet "JOB CHECK" in Cell F1.


Appreciate you help as always!!!!

 

[ccrcarey]

All good. it work perfectly now, much appreciated, cheers Chris

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

All good. it work perfectly now, much appreciated, cheers Chris

 

[thezoomman]

Hi, noob to VBA but have adjusted and used your script above which does nearly everything I need.
The additional step I need is to paste the copied cell as value as the original cell has a formula in it. This would make it perfect!
Thank you!

 

[navarreman1]

I am trying something similar but have zero macro knowledge but have managed to get it to replicate a double click in column A to the last row of column A using the above macro but modified (see below). What I need is for columns A7:A30 thru E7:E30 & H7:H30 to fill A6-E6 & H6 in the respective column (same sheet - "Sheet2") which then feed a Concatenate formula that defines a file naming convention. The Concatenate works just fine, just want an easier method than drop down lists to fill the target cells. The data below is only representative and not the actual data. It will extend to line 30 when filled. Other cells are not involved in the operation I want to achieve.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2

3

4

5

LOCATION

MFG

LVL1

LVL2

LVL3

BLDG

RM

USE

6

7

A1

ACME

10

SAM

AA

CLS

8

A2

JONES

11

ALI

BB

CR

9

A3

BROWN

12

MIKE

CC

CCR

10

A4

13

BOB

DD

GCR

11

A5

NANCY

EE

BCR

12

A6

VICKY

FF

JOC

13

TOM

GG

NOC

14

HH

15

II

16

JJ

17

KK

18

LL

19

20

21

22

23

24

25

26

27

28

29

30

<tbody>
</tbody>

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified 8/28/2018 10:57:21 AM EDT
If Not Intersect(Target, Range("A7:A30")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim Lastrow As Long
Lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Target.Copy Sheets("Sheet2").Cells(Lastrow, 1)
End If
End Sub

 

[mgabr]

Put this script in sheet named
Options

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  8/28/2018  10:57:21 AM  EDT
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim Lastrow As Long
Lastrow = Sheets("List").Cells(Rows.Count, "A").End(xlUp).Row + 1
Target.Copy Sheets("List").Cells(Lastrow, 1)
End If
End Sub

I am trying to do something similar except I would like the contents from column C in Sheet 1 to populate the contents in column E starting from row 15 and on in sheet 2 when I double click an item. Also, is there a way to do something similar without Macros? Thanks.

 

[My Aswer Is This]

So this script will copy the contents of Sheet1 column C when doubled clicked on column C of sheet1
And the value will be copied to sheet2 column E starting in row 15

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet1 tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  12/6/2019  4:22:12 PM  EST
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Cancel = True
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "E").End(xlUp).Row + 1
If Lastrow < 14 Then Lastrow = 15
Target.Copy Sheets(2).Cells(Lastrow, "E")
End If
End Sub

 

[LannyN]

I am trying to get this to work within the same sheet. I have a range of unique values in a column H. I want to be able to double click on a cell in that column and have the value copy to the last empty cell in column K. I tried editing the above script but could not get it working. Any suggestions?

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Not Intersect(Target, Range("H:H")) Is Nothing Then
      Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Target
      Cancel = True
   End If
End Sub

 

[LannyN]

That did the trick. Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.