Click a cell to add a string to the bottom of a list

Question

I'm very new to using macros in Excel, so I hope this question isn't too silly.

I'm creating a worksheet to track sales. I've got a list of drinks in one column and I wanted to assign a macro to the cells so that when you click on them, the text in their cells is copied to another column.

I know you can record macros to copy-and-paste values, but I'm not sure how to make it copy the text in the next empty cell in the column, and not just in the first cell.

So in summary, these are my tables. I want to be able to click a cell in the Drinks column, and have the string appear at the bottom of the list column (so after 'Cuba Libre')

Thanks!

EDIT_1:

Ok, so here's my code so far:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Address = "$C$2" Then
       Range("C2").Select
       Selection.Copy
       Range("A2").Select
       ActiveSheet.Paste
   End If
End Sub

^I've repeated this code for each of the relevant cells in column C (C2:C5).

Like I said, I've only gotten to the point of being able to copy paste the values from the Drinks column into the List column, I am lost as to how to paste the value into the next empty cell.

Answer 1

You need to expand your check on Target to include the whole Drinks range. Then determine the next available cell in List

Something like (hard coded for List in Column A and Drinks in Column C)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rDrinks As Range
    
    ' single cell has been selected
    If Target.Cells.Count = 1 Then
        ' Get ref to Drinks range
        Set rDrinks = Me.Range(Me.Cells(2, 3), Me.Cells(Me.Rows.Count, 3).End(xlUp))
        ' Is selected cell in drinks range?
        If Not Application.Intersect(Target, rDrinks) Is Nothing Then
            ' add to list
            Me.Cells(Me.Rows.Count, 1).End(xlUp).Offset(1, 0).Value2 = Target.Value2
        End If
    End If
End Sub