Private Sub CommandButton1_Click()
Dim lStart As Long, lEnd As Long
If TextBox1.Value = "" Then: MsgBox "Missing First Name entry.": Exit Sub
If TextBox2.Value = "" Then: MsgBox "Missing Last Name entry.": Exit Sub
If TextBox3.Value = "" Then: MsgBox "Missing Unit entry.": Exit Sub
With Worksheets("all")
If .Range("A:A").Find(TextBox3.Value, , , xlWhole, xlByRows, xlNext, False) Is Nothing Then
MsgBox "Unit not found."
Exit Sub
End If
lStart = .Range("A:A").Find(TextBox3.Value, , , xlWhole, xlByRows, xlNext, False).Row
lEnd = .Range("A:A").Find(TextBox3.Value, , , xlWhole, xlByRows, xlPrevious, False).Row
Application.ScreenUpdating = False
.Rows(lEnd + 1).Insert
lEnd = lEnd + 1
.Range("A" & lEnd).Value = .Range("A" & lStart).Value
.Range("C" & lEnd).Value = TextBox1.Value & " " & TextBox2.Value
.Range("I" & lEnd).Value = TextBox2.Value
'sort
.Rows(lStart & ":" & lEnd).Sort Key1:=.Range("I" & lStart), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.ScreenUpdating = True
End With
End Sub
Bookmarks