Hi all
I am pasting data from 2 columns in a listbox to an excel spreadsheet. Is there a way I can avoid duplicate entries into the worksheet if they are already entered? I would also like to be able to tell the user which entry is a duplicate.
Many thanks
I am pasting data from 2 columns in a listbox to an excel spreadsheet. Is there a way I can avoid duplicate entries into the worksheet if they are already entered? I would also like to be able to tell the user which entry is a duplicate.
Many thanks
VBA Code:
Private Sub CommandButton4_Click() 'paste selected items in listbox2 to excel worksheet
Application.ScreenUpdating = False
Dim I As Long
For I = 0 To Me.ListBox2.ListCount - 1
ListBox2.Selected(I) = True
Next
Dim rngNext As Range
Dim col As Long
Set rngNext = Worksheets("data").Range("E" & Rows.Count).End(xlUp).Offset(1)
For I = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(I) Then
For col = 0 To ListBox2.ColumnCount - 1
rngNext.Offset(, col).Value = ListBox2.List(I, col)
Next col
Set rngNext = rngNext.Offset(1)
ListBox2.Selected(I) = False
End If
Next I
Application.ScreenUpdating = True
BeforeExit:
Set rngNext = Nothing