This will add the item to the bottom of the list. Uncomment the Green sort code if you want the list sorted each time a new item is added.
If you haven't already done so, make your dropdown list dynamic.
Create a Dynamic Named Range
To install the code:- Right-click on the Sheet1 tab.
- Select View Code from the pop-up context menu.
- Paste the code from below in the worksheet's code module.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A2" Then
If Target.Value <> Empty Then
With Sheets("Sheet2")
If .Range("A:A").Find(Target, , , xlWhole, 1, 1, 0) Is Nothing Then
.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value
MsgBox Target.Value, vbInformation, "Item Added To List"
' .Range("A:A").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlYes, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
' DataOption1:=xlSortNormal
Target.ClearContents
Else
MsgBox Target.Value, vbExclamation, "Item Already On List"
End If
End With
End If
End If
End Sub
Bookmarks