Double click cell to add data to next empty row in different worksheet script

[Rextus]

I'm trying to make sort of a shopping cart. The goods are on a sheet called "Goods" and start from row 2 to row 594. There is a cell besides each item (range "E2:E594"). When I double click the cell, it transfers the data in the cells to the left to another worksheet called "Cart" on row 34 and displays a message.

I have a partially working script but only for the first row of the Cart worksheet (row 34). See the code below if it can be of any help.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 'Check if double clicked cell is within column E of Goods spreadsheet
    If Intersect(Target, Range("E2:E594")) Is Nothing Then Exit Sub
'Copy data from Goods to Cart
    Sheets("Cart").Range("D34") = Cells(Target.Row, 5) 
    Sheets("Cart").Range("C34") = Cells(Target.Row, 4) 
    If Target.Locked Then 'Only display message is cells in column E of Goods spreadsheet is pressed
        Range("A1").Activate
        MsgBox "Item code pasted to Spare Parts worksheet successfully", vbInformation, "Paste success"
    End If
End Sub

The problem is that if I then go back to the List worksheet and "add" another item by clicking another row, that new selection overrides the information in row 34 of the "Cart" worksheet. I'd like to have the info from the second item inserted to the 35th row in cells C35 and D35.

Example:

"Goods" spreadsheet

	A	B	C	D	E (these cells are locked to prevent modification)
1	Category	Brand	Product	Can spoil	Double click to add item description to cart
2	Groceries	Bready	Bread	Yes	=CONCAT(B2," ",C2)
3	Fluids	Soupy	Soup	No	Soupy Soup
4	PPE	Glovey	Glove	No	Glovey Glove

"Cart" spreadsheet

	A	B	C	D
33	Item number	In stock	Can spoil	Item Description
34	Item 1
35	Item 2
36	Item 3

When I double click on cell E3 in the "Goods" spreadsheet, I want to have the following in "Cart"

	A	B	C	D
33	Item number	In stock	Can spoil	Item Description
34	Item 1		No	Soupy Soup
35	Item 2
36	Item 3

If I go back and double click on cell E4 in the "Goods" spreadsheet, I want to have the following in "Cart"

	A	B	C	D
33	Item number	In stock	Can spoil	Item Description
34	Item 1		No	Soupy Soup
35	Item 2		No	Glovey Glove
36	Item 3

Using the script I have currently (shown above), row 34 will get overwritten. I'd like to have the script write to row 35 instead. Please let me know if you need more explanations.

TIA.

 

[Rextus]

Bump

 

[Joe4]

Does this do what you want?

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

    Dim nr As Long

'Check if double clicked cell is within column E of Goods spreadsheet
    If Intersect(Target, Range("E2:E594")) Is Nothing Then Exit Sub

'Find next available row on "Cart" sheet
    nr = Sheets("Cart").Cells(Rows.Count, "C").End(xlUp).Row + 1
    If nr < 34 Then nr = 34
    
'Copy data from Goods to Cart
    Sheets("Cart").Cells(nr, "D") = Cells(Target.Row, 5)
    Sheets("Cart").Cells(nr, "C") = Cells(Target.Row, 4)
    If Target.Locked Then 'Only display message is cells in column E of Goods spreadsheet is pressed
        Range("A1").Activate
        MsgBox "Item code pasted to Spare Parts worksheet successfully", vbInformation, "Paste success"
    End If
    
End Sub