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!!!!

 

[wideboydixon]

Right click the "Main Sheet" tab and select "View Code" then paste the following in to VBA:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = 1 And Target.Column = 4 Then
    Sheets("JOB CHECK").Range("F1").Value = Target.Value
End If

End Sub

That will do what you're asking I believe.

WBD

 

[My Aswer Is This]

Try this:
When you double click on any cell in column "D"
This script will do what you want.

This is a auto sheet event script
Your Workbook must be Macro enabled
To install this code:

Right-click on the sheet Main Sheet 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)
Cancel = True
On Error GoTo M
If Not Intersect(Target, Range("D:D")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Sheets("JOB CHECK").Range("F1").Value = Target.Value
End If
Exit Sub
M:
MsgBox "You have no sheet named JOB CHECK "
End Sub

 

[brumby]

perfect!!!! thank you all

 

[ccrcarey]

Hi Folks, in the above auto script I would like it to do exactly what its doing now but also to build a list in F1, F2, F3, and down the column. From D:D with a list of all my different cell values IÂ’d like it to build a list in F when I randomly clicking cells in D and put them one after the other in F. Any help would be much appreciated, Chris

A thought, even better if possible, when I delete the continence of cell in F list (the cell is made empty) it will then automatically moves the list upwards so there are no blank cells in the list it creates

 

[My Aswer Is This]

Chris this does what you asked for in your first request. But does not copy to another sheet.
But then you seemed to say it would be even better if the script could do this:

So I'm not sure if you wanted both things or the second request and not the first request.

And also you said you wanted exactly the same.
So are you saying you have a sheet named "Job CHECK"

Sheets("JOB CHECK").Range("F1").Value = Target.Value

It may be clearer to say exactly what you want instead of saying do exactly as this but then do this and or this.


​

 

[ccrcarey]

Chris this does what you asked for in your first request. But does not copy to another sheet.
But then you seemed to say it would be even better if the script could do this:

So I'm not sure if you wanted both things or the second request and not the first request.

And also you said you wanted exactly the same.
So are you saying you have a sheet named "Job CHECK"

Sheets("JOB CHECK").Range("F1").Value = Target.Value

It may be clearer to say exactly what you want instead of saying do exactly as this but then do this and or this.


​

Thanks for the reply, I have a book with two sheets, sheet “A”and Sheet “B” I want to create a permanent list in sheet “A” A1,2,3,,4 and soon down to 100. Then when I click on any item in this list I want it to populatea list in sheet “B” A1,2,3,4 and so on down to 100 without any blank cells. I’m using the book to give to people when ordering a vehicle, sheet “A” will have all of the possible options to choose from and sheet “B” will have a list of what they have chosen. Hope this makes it clearer, cheers Chris

 

[My Aswer Is This]

Assuming your sheet is named B
That's a odd name to give a sheet so modify script if needed try this
Put this script in sheet named A

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

When you double click on any cell in column A of sheet A that cells value will be copied to sheet named B Column A

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Modified  8/26/2018  11:44:35 PM  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("B").Cells(Rows.Count, "A").End(xlUp).Row + 1
Target.Copy Sheets("B").Cells(Lastrow, 1)
End If
End Sub

 

[ccrcarey]

Thank you very much for doing that, it works perfectly.Could you edit it so that it works with sheet A named as “Options” and sheet Bnamed as “List” please. When I change the names of the sheets and change the “A” and the two “B”’s in the code to match the sheetnames it didn’t work, thanks Chris

 

[My Aswer Is This]

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