clicking on a cell on one sheet will copy that row's data to another sheet

[steve400243]

Hello, I'm not sure how to go about getting this to work as needed. I get different size reports sent to me like the one in "report" tab. It can vary in how many rows, but this is a sample of how it gets to me. when I click on any cell in column A with data, (Order# row) I want that row to copy over to the Data tab below the last entry in the table. Thanks for any help provided.

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Order #	PO #	Sales Order #	Ship To	City	State	Zipcode	Carrier	Tracking
2	02971697	042220223		THE EYE CENTER OF N. FLORIDA	PANAMA CITY	FL	32405	FDEG	548407254179, 548407254180, 548407254190, 548407254205, 548407254216, 548407254227
3	02972523	032520224/0425	032520224/0425	CALLAHAN EYE HOSPITAL	BIRMINGHAM	AL	35233-181	FDEG	548407254098, 548407254102
4	02972534	042520221	042520221	THE EYE CENTER OF N. FLORIDA	PANAMA CITY	FL	32405	FDEG	548407254113, 548407254124, 548407254135, 548407254146, 548407254157, 548407254168
5	02973094	042620221	042620221	NORWICH OPHTHALMOLOGY GROUP	NORWICH	CT	06360	FDEG	548407254282, 548407254293
6	02973097	042620222	042620222	OC EYE ASSOCIATES	IRVINE	CA	92606	FDEG	548407254308
report

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	ORDER	PO#	SALES ORDER NUMBER	SHIP TO	CITY	STATE	ZIP	CARRIER	TRACKING	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11
3	2971697	42220223		THE EYE CENTER OF N. FLORIDA	PANAMA CITY	FL	32405	FDEG	548407254179, 548407254180, 548407254190, 548407254205, 548407254216, 548407254227	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
Data

Cell Formulas
Range	Formula
R3	R3	=[@Column9]-[@Column10]

 

[steve400243]

I found this code online but cannot get it to work. Any ideas?

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


If Target.Column = 1 Then
    On Error Resume Next
    Target.EntireRow.Copy Sheets("report").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)


    If Err.Number <> 0 Then
        Target.EntireRow.Copy Sheets("data").Range("A1")
    End If
    On Error GoTo 0

End If

End Sub

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 1 Then
      Target.EntireRow.Copy Sheets("Data").Cells(rows.Count, "A").End(xlUp).Offset(1)
   End If
End Sub

This needs to go in the Report sheet code module.

 

[steve400243]

Thank you for your time in looking at this Fluff. I appreciate it. I have the DATA page in a table format, is there a way to have the new rows, as they are copied over, extend the table. if that makes sense?

 

[Fluff]

Ok, how about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim LstRw As ListRow
   If Target.Column = 1 Then
      Cancel = True
      Set LstRw = Sheets("Data").ListObjects(1).ListRows.Add
      Target.Resize(, 9).Copy LstRw.Range(1)
   End If
End Sub

 

[steve400243]

Ok, how about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Dim LstRw As ListRow
   If Target.Column = 1 Then
      Cancel = True
      Set LstRw = Sheets("Data").ListObjects(1).ListRows.Add
      Target.Resize(, 9).Copy LstRw.Range(1)
   End If
End Sub

Excellent, Thank you again! this will work as needed. Have a great day.

 

[Fluff]

You're welcome & thanks for the feedback.