Copy Cells On Double Click

Try copying this into the code for Sheet2 (right click on the page tab and select ViewCode from the menu that appears)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Not Application.Intersect(Target, Cells(3, 1)) Is Nothing Then _
Sheets("sheet3").Cells(5, 2) = Target
If Not Application.Intersect(Target, Cells(5, 1)) Is Nothing Then _
Sheets("sheet3").Cells(5, 2) = Target
If Not Application.Intersect(Target, Cells(3, 2)) Is Nothing Then _
Sheets("sheet3").Cells(6, 2) = Target
End Sub

Any help?

Regards

Thanks for your help but it's not what I'm looking for. I don't wanna fill in every cell first in the formula cause there are just too much cells. Do you know if there's an other way without having to give up every cell first? Thanks for your help!

NoIdea

Hmmm...is there any pattern in where you want the cells to copy to in the destination sheet?

Hi,

Yeah there is a pattern but it's different for each column. Like Column A is cell D4, Column B is cell D6,...., Column AC is cell N34. So there are a lot of Columns and cells but there is a pattern! I hope you know a way I can let it work with the Double click? Thanks!

NoIdea Hmmm...is there any pattern in where you want the cells to copy to in the destination sheet?

I think you'll still have to set source/targets, but if the sources are columns, it may not take that long.
Try this - it will do what you have specified but you'll need to expand it to take in all columns etc.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Not Application.Intersect(Target, Columns(1)) Is Nothing Then _
Sheets("sheet3").Cells(4, 4) = Target
If Not Application.Intersect(Target, Columns(2)) Is Nothing Then _
Sheets("sheet3").Cells(6, 4) = Target
If Not Application.Intersect(Target, Columns(29)) Is Nothing Then _
Sheets("sheet3").Cells(34, 14) = Target
End Sub

If this is not enough, it may help if you post why you need to do this/what you are trying to achieve.

Any help?

Regards

It's not really working. It only puts cells of row 3 in the right cells. The reason I need this is cause the sheet you can double click are all the data of customers, every row is the data of 1 customer, and the question to me was when they double click on a customer no matter what cell that all the data of that customer will appear. All the data in that row. I hope this is the info you need to help me out? Thanks!

NoIdea I think you'll still have to set source/targets, but if the sources are columns, it may not take that long.

: Hi, : Yeah there is a pattern but it's different for each column. Like Column A is cell D4, Column B is cell D6,...., Column AC is cell N34. So there are a lot of Columns and cells but there is a pattern! I hope you know a way I can let it work with the Double click? Thanks! : NoIdea

I think I see what you want to do.

You have rows of data in Sheet2, each one being for a separate customer. If a user double clicks on any
cell, you want the complete row of data for that customer to appear in Sheet 3.
I am not sure, but I think you want it to appear in the same relative position in Sheet3 (if this part is incorrect,
the code needs to be amended with the required destination).

Try this code in sheet2:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
With Worksheets("Sheet2").UsedRange.Cells
Dim r As Long
r = Target.Row
For Each c In Rows(r).Cells
b = c.Address
Worksheets("Sheet3").Range(b) = c
Next c
End With

End Sub

Does that do it?

Regards

It's not really working. It only puts cells of row 3 in the right cells. The reason I need this is cause the sheet you can double click are all the data of customers, every row is the data of 1 customer, and the question to me was when they double click on a customer no matter what cell that all the data of that customer will appear. All the data in that row. I hope this is the info you need to help me out? Thanks! : I think you'll still have to set source/targets, but if the sources are columns, it may not take that long. : Try this - it will do what you have specified but you'll need to expand it to take in all columns etc. : Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) : If Not Application.Intersect(Target, Columns(1)) Is Nothing Then _ : Sheets("sheet3").Cells(4, 4) = Target : If Not Application.Intersect(Target, Columns(2)) Is Nothing Then _ : Sheets("sheet3").Cells(6, 4) = Target : If Not Application.Intersect(Target, Columns(29)) Is Nothing Then _ : Sheets("sheet3").Cells(34, 14) = Target : End Sub : If this is not enough, it may help if you post why you need to do this/what you are trying to achieve. : Any help? : Regards :

Thanks but it's still not what I want. I do want it to be shown in a different position. That The data of Column A appears in Cell D4, Column B in D6, Column C in D8, Column D in D10, Column E in I10, etc. Is there also a way to make that happen? Thanks! I think I see what you want to do. You have rows of data in Sheet2, each one being for a separate customer. If a user double clicks on any cell, you want the complete row of data for that customer to appear in Sheet 3. I am not sure, but I think you want it to appear in the same relative position in Sheet3 (if this part is incorrect, the code needs to be amended with the required destination). Try this code in sheet2: With Worksheets("Sheet2").UsedRange.Cells Dim r As Long r = Target.Row For Each c In Rows(r).Cells b = c.Address Worksheets("Sheet3").Range(b) = c Next c End With End Sub Does that do it?

Are you saying you want all data in a column to appear in a cell
e.g. all the cell contents in ColumnB to be in D6?

How much data is there in each Column?

There seems to be some pattern for Columns A/B/C/D going to D4/6/8/10 but then
ColumnE suddenly goes off to I10.
Also, you seem to want A/B/C to go to ColumnD, and then all of ColumnD to go to D10.

Is my understanding correct, or am I missing something?

Regards

Thanks but it's still not what I want. I do want it to be shown in a different position. That The data of Column A appears in Cell D4, Column B in D6, Column C in D8, Column D in D10, Column E in I10, etc. Is there also a way to make that happen? Thanks! : I think I see what you want to do. : You have rows of data in Sheet2, each one being for a separate customer. If a user double clicks on any : cell, you want the complete row of data for that customer to appear in Sheet 3. : I am not sure, but I think you want it to appear in the same relative position in Sheet3 (if this part is incorrect, : the code needs to be amended with the required destination). : Try this code in sheet2

I don't want all the data of a column to appear in a cell I want just the 1 cell of a column of a special customer to be shown in a cell on the other sheet. And the data in the column is variable and it can be over a hundred but also over a thousand. It all must be possible. And yeah there is a pattern. It's like all kind of fiels I made with Name and that's column A and I want that to be shown in cell D4. And that I have with all kind of data of the customer. But when you click on a cell I only want the data of 1 customer to be shown in the right pattern. Hope you can help me out? Thanks.

NoIdea Are you saying you want all data in a column to appear in a cell e.g. all the cell contents in ColumnB to be in D6? How much data is there in each Column? There seems to be some pattern for Columns A/B/C/D going to D4/6/8/10 but then ColumnE suddenly goes off to I10. Also, you seem to want A/B/C to go to ColumnD, and then all of ColumnD to go to D10. Is my understanding correct, or am I missing something?

I think I'm starting to understand.

It may be possible to do this, but the destination pattern must be known.

Given that each row contains data re one customer, how many columns does each customer have?
For each column, you need to say which Cell you want as the destination:
e.g. ColA goes to D4, ColB to D6 etc for every column in which there may be data.

As you have set up the sheets this way, I am sure you have the destinations planned. Once I
have this information, I'm sure we can produce what you want.

Regards

I don't want all the data of a column to appear in a cell I want just the 1 cell of a column of a special customer to be shown in a cell on the other sheet. And the data in the column is variable and it can be over a hundred but also over a thousand. It all must be possible. And yeah there is a pattern. It's like all kind of fiels I made with Name and that's column A and I want that to be shown in cell D4. And that I have with all kind of data of the customer. But when you click on a cell I only want the data of 1 customer to be shown in the right pattern. Hope you can help me out? Thanks. : Are you saying you want all data in a column to appear in a cell : e.g. all the cell contents in ColumnB to be in D6? : How much data is there in each Column? : There seems to be some pattern for Columns A/B/C/D going to D4/6/8/10 but then : ColumnE suddenly goes off to I10. : Also, you seem to want A/B/C to go to ColumnD, and then all of ColumnD to go to D10. : Is my understanding correct, or am I missing something? : Regards

Okay here are the columns with the destinations! But know that the destinations are on the other sheet!

Col Cell
A - D4
B - D6
C - D8
D - D10
E - I10
F - D12
G - I12
H - M12
I - D14
J - D16
K - I16
L - M16
M - E18
N - E20
O - E22
P - E24
Q - D26
R - D28
S - D30
T - D32
U - I34
V - I28
W - I30
X - I32
Y - J34
Z - M28
AA - M30
AB - M32
AC - N34

That are all the columns with the destination cells. Hope you can help me with this? Thanks

NoIdea

I think I'm starting to understand. It may be possible to do this, but the destination pattern must be known. Given that each row contains data re one customer, how many columns does each customer have? For each column, you need to say which Cell you want as the destination: e.g. ColA goes to D4, ColB to D6 etc for every column in which there may be data. As you have set up the sheets this way, I am sure you have the destinations planned. Once I have this information, I'm sure we can produce what you want.

I think we may finally have what you are after.

Again, this goes in Sheet2 code.

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

With Worksheets("Sheet2").UsedRange.Cells
Dim r As Long, cl As Integer
r = Target.Row
For Each c In Rows(r).Cells
cl = c.Column
Select Case cl
Case 1
Worksheets("Sheet3").Range("D4") = c
Case 2
Worksheets("Sheet3").Range("D6") = c
Case 3
Worksheets("Sheet3").Range("D8") = c
Case 4
Worksheets("Sheet3").Range("D10") = c
Case 5
Worksheets("Sheet3").Range("I10") = c
Case 6
Worksheets("Sheet3").Range("D12") = c
Case 7
Worksheets("Sheet3").Range("I12") = c
Case 8
Worksheets("Sheet3").Range("M12") = c
Case 9
Worksheets("Sheet3").Range("D14") = c
Case 10
Worksheets("Sheet3").Range("D16") = c
Case 11
Worksheets("Sheet3").Range("I16") = c
Case 12
Worksheets("Sheet3").Range("M16") = c
Case 13
Worksheets("Sheet3").Range("E18") = c
Case 14
Worksheets("Sheet3").Range("E20") = c
Case 15
Worksheets("Sheet3").Range("E22") = c
Case 16
Worksheets("Sheet3").Range("E24") = c
Case 17
Worksheets("Sheet3").Range("D26") = c
Case 18
Worksheets("Sheet3").Range("D28") = c
Case 19
Worksheets("Sheet3").Range("D30") = c
Case 20
Worksheets("Sheet3").Range("D32") = c
Case 21
Worksheets("Sheet3").Range("I34") = c
Case 22
Worksheets("Sheet3").Range("I28") = c
Case 23
Worksheets("Sheet3").Range("I30") = c
Case 24
Worksheets("Sheet3").Range("I32") = c
Case 25
Worksheets("Sheet3").Range("J34") = c
Case 26
Worksheets("Sheet3").Range("M28") = c
Case 27
Worksheets("Sheet3").Range("M30") = c
Case 28
Worksheets("Sheet3").Range("M32") = c
Case 29
Worksheets("Sheet3").Range("N34") = c
Case Else
Exit Sub
End Select
Next c
End With

End Sub

Does that do it?

Regards

Okay here are the columns with the destinations! But know that the destinations are on the other sheet! Col Cell A - D4 B - D6 C - D8 D - D10 E - I10 F - D12 G - I12 H - M12 I - D14 J - D16 K - I16 L - M16 M - E18 N - E20 O - E22 P - E24 Q - D26 R - D28 S - D30 T - D32 U - I34 V - I28 W - I30 X - I32 Y - J34 Z - M28 AA - M30 AB - M32 AC - N34 That are all the columns with the destination cells. Hope you can help me with this? Thanks : I think I'm starting to understand. : It may be possible to do this, but the destination pattern must be known. : Given that each row contains data re one customer, how many columns does each customer have? : For each column, you need to say which Cell you want as the destination

IT WORKS!!! THANKS A LOT!!! YOU"RE THE MAN!!!! THANKS!!!!

NoIdea

I think we may finally have what you are after. Again, this goes in Sheet2 code. With Worksheets("Sheet2").UsedRange.Cells Dim r As Long, cl As Integer r = Target.Row For Each c In Rows(r).Cells cl = c.Column Select Case cl Case 1 Worksheets("Sheet3").Range("D4") = c Case 2 Worksheets("Sheet3").Range("D6") = c Case 3 Worksheets("Sheet3").Range("D8") = c Case 4 Worksheets("Sheet3").Range("D10") = c Case 5 Worksheets("Sheet3").Range("I10") = c Case 6 Worksheets("Sheet3").Range("D12") = c Case 7 Worksheets("Sheet3").Range("I12") = c Case 8 Worksheets("Sheet3").Range("M12") = c Case 9 Worksheets("Sheet3").Range("D14") = c Case 10 Worksheets("Sheet3").Range("D16") = c Case 11 Worksheets("Sheet3").Range("I16") = c Case 12 Worksheets("Sheet3").Range("M16") = c Case 13 Worksheets("Sheet3").Range("E18") = c Case 14 Worksheets("Sheet3").Range("E20") = c Case 15 Worksheets("Sheet3").Range("E22") = c Case 16 Worksheets("Sheet3").Range("E24") = c Case 17 Worksheets("Sheet3").Range("D26") = c Case 18 Worksheets("Sheet3").Range("D28") = c Case 19 Worksheets("Sheet3").Range("D30") = c Case 20 Worksheets("Sheet3").Range("D32") = c Case 21 Worksheets("Sheet3").Range("I34") = c Case 22 Worksheets("Sheet3").Range("I28") = c Case 23 Worksheets("Sheet3").Range("I30") = c Case 24 Worksheets("Sheet3").Range("I32") = c Case 25 Worksheets("Sheet3").Range("J34") = c Case 26 Worksheets("Sheet3").Range("M28") = c Case 27 Worksheets("Sheet3").Range("M30") = c Case 28 Worksheets("Sheet3").Range("M32") = c Case 29 Worksheets("Sheet3").Range("N34") = c Case Else Exit Sub End Select Next c End With End Sub Does that do it? : A - D4 : B - D6 : C - D8 : D - D10 : E - I10 : F - D12 : G - I12 : H - M12 : I - D14 : J - D16 : K - I16 : L - M16 : M - E18 : N - E20 : O - E22 : P - E24 : Q - D26 : R - D28 : S - D30 : T - D32 : U - I34 : V - I28 : W - I30 : X - I32 : Y - J34 : Z - M28 : AA - M30 : AB - M32 : AC - N34 : That are all the columns with the destination cells. Hope you can help me with this? Thanks : NoIdea :