check for new item in a list

[neilp]

Hi

I have a sheet (2023) that has a a list of products in column C. This list is added to frequently. Most times, the product already exists. Is there a way that if a "New" product is added, it is automatically copied over to Sheet2, column A, next unused row?

I'm thinking a worksheet change event, but it is not something I'm familiar with

thanks

Neil

 

[Snakehips]

@neilp Try like below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim nr As Long
Dim mtch As Long
Dim ws As Worksheet
Dim ProdLst As Range

If Not Target.Column = 2 Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = vbNullString Then Exit Sub

On Error Resume Next
Set ws = Sheets("Sheet2")
nr = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
Set ProdLst = ws.Range("A2:A" & nr)
mtch = WorksheetFunction.Match(Target, ProdLst, 0)

If Not mtch > 0 Then ws.Cells(nr, 1) = Target

On Error GoTo 0
End Sub

Hope that helps.

 

[neilp]

thanks @Snakehips - needed the slightest tweak, but works great.