code for clicking a cell in a table that goes to a new sheet and pulls up data about the selected cell from the same row.

[Sonican]

So basically I have a spreadsheet that is imported from a access database. it has 13 columns and a few thousand rows, but the exact number of rows will vary based on the referenced time frame from a database. in the first column is an identification number, and in the following columns is a bunch of data about that Id number. I need to be able to double click the ID number and have it go to sheet 2 in the excel document and pull up 3 of the statistics from that ID number as well as the id number its self. also, when i click a second ID number, i need the same data to be displayed below the first ID number.

Example data

Lets say I click ID numbers 0003 and 0015

Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]ID number (pull up in sheet 2)[/TD]
[TD]stat 1 (Pull up in sheet 2)[/TD]
[TD]stat 2 (pull up in sheet 2)[/TD]
[TD]stat 3 (pull up in sheet 2)[/TD]
[TD]stat 4 [/TD]
[TD]stat 5[/TD]
[TD]stat 6[/TD]
[TD]stat 7[/TD]
[TD]stat 8[/TD]
[TD]stat 9[/TD]
[TD]stat 10[/TD]
[TD]stat 11[/TD]
[TD]stat 12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0001
[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0002[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]>>[/TD]
[TD]0003[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0004[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0005[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0006[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0007[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0008[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0009[/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0010[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0011[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0012[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0013[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0014[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]>>[/TD]
[TD]0015[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0016[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0017[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0018[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0019[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

after double clicking

Sheet 2[TABLE="width: 500"]
<tbody>[TR]
[TD]ID number[/TD]
[TD]stat 1[/TD]
[TD]stat 2[/TD]
[TD]stat 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0015[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I will be honest I am lost when it comes to Visual Basic. any help would be greatly appreciated.

 

[j_unsuitable]

Hey,

you can try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim LastRow as Long
If Target.Column <> 1 Then Exit Sub
LastRow = Worksheets("[COLOR=#ff0000]Sheet 2[/COLOR]").Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Cells(Target.Row, 1).Resize(1, 4).Copy _
Destination:=Worksheets("[COLOR=#ff0000]Sheet 2[/COLOR]").Cells(LastRow + 1, 1)
Cancel = True
Application.ScreenUpdating = True

End Sub

This assumes the ID Number is in column A and only works if you double click a cell in column A.
You need to enter this code by right clicking on the tab for Sheet 1 > View code (this will open Microsoft Visual Basic, with a blank window, just paste the code in there)
Change the red parts to the actual name of the Sheet you want to paste the cells to.

Julian