Adding a sort to userform code

Hi all I have a userform to add new records to a worksheet. It works fine, but assigns a reference number based on that of the previous record. If the worksheet isn't sorted correctly it gives an incorrect reference number.
I thought this would be simple however whenever I come to add new code to this routine I receive errors in the already written and working parts of the code( Type Mismatch). I can only imagine this means that although the routine works, it does have errors in the way its written.


Can someone please tell me what I have missed?

Private Sub CommandButton1_Click()
If Worksheets("Master").FilterMode = True Then Sheets("Master").ShowAllData
Dim rw As Long
Dim NR As Long
   With Sheets("master")
      rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
      If TextBox1.Value = nul Then
      MsgBox "Please enter title"
      Exit Sub
      Else
        If TextBox3.Value = nul Then
        MsgBox "Please enter surname"
        Exit Sub
        Else
            If TextBox4.Value = nul Then
            MsgBox "Please enter house number / name"
            Exit Sub
            Else
              If TextBox5.Value = nul Then
                MsgBox "Please enter road1"
                Exit Sub
                Else
                    If TextBox6.Value = nul Then
                    MsgBox "Please enter road2"
                    Exit Sub
                    Else
                        If TextBox7.Value = nul Then
                        MsgBox "Please enter FULL postcode"
                        Exit Sub
                        Else
                            If TextBox8.Value = nul Then
                            MsgBox "Please enter FULL postcode"
                            Exit Sub
                            Else
                                If TextBox9.Value = nul Then
                                MsgBox "Please enter FULL postcode"
                                Exit Sub
                                Else
                                    If TextBox10.Value = nul Then
                                    MsgBox "Please enter FULL postcode"
                                    Exit Sub
                                    Else
                                        If TextBox11.Value = nul Then
                                        MsgBox "Please enter atleast one phone number"
                                        Exit Sub
                                        Else
    End If
        End If
            End If
                End If
                    End If
                        End If
                            End If
                                End If
                                    End If
                                        End If
                                        
      
      If OptionButton1.Value = True Then .Range("w" & rw) = "4"
      If OptionButton2.Value = True Then .Range("w" & rw) = "8"
      If OptionButton3.Value = True Then .Range("w" & rw) = "12"
      .Range("b" & rw).Value = Date - 1
      .Range("c" & rw).Value = TextBox1.Value
      .Range("d" & rw).Value = TextBox2.Value
      .Range("e" & rw).Value = TextBox3.Value
      .Range("f" & rw).Value = TextBox4.Value
      .Range("g" & rw).Value = TextBox5.Value
      .Range("h" & rw).Value = TextBox6.Value
      .Range("i" & rw).Value = TextBox7.Value
      .Range("j" & rw).Value = TextBox8.Value
      .Range("k" & rw).Value = TextBox9.Value
      .Range("l" & rw).Value = TextBox10.Value
      .Range("n" & rw).Value = TextBox11.Value
      .Range("o" & rw).Value = TextBox12.Value
      .Range("p" & rw).Value = TextBox13.Value
      .Range("q" & rw).Value = TextBox14.Value
      .Range("ab" & rw).Value = TextBox15.Value
      .Range("s" & rw).Value = ComboBox1.Value
      .Range("m" & rw).Value = TextBox7.Value & TextBox8.Value & " " & TextBox9.Value & " " & TextBox10.Value
      
      .Range("T" & rw) = "l2"
      .Range("u" & rw) = .Range("u" & rw - 1) + 1
      .Range("a" & rw) = .Range("u" & rw - 1) + 1
      .Range("aa" & rw) = Calendar1.Value
      MsgBox "record added to Master database" & vbNewLine & "Unique Reference l2" & " " & .Range("u" & rw)
    End With
    If MsgBox("Add to Mail Merge?", vbYesNo + vbQuestion, "UKWC Production") = vbYes Then
Dim MyBook As Workbook, newBook As Workbook
    Dim FileNm As String

    Set MyBook = ThisWorkbook

    FileNm = ThisWorkbook.Path & "\" & "mm.xlsm"
    Set newBook = Workbooks.Open(FileNm)
    With newBook
        NR = Sheets("MASTERmm").Range("A" & Rows.Count).End(xlUp).Row + 1
        
        MyBook.Sheets("master").Rows(rw).Copy .Sheets("mastermm").Rows(NR)
         Application.Visible = False
         Application.DisplayAlerts = False
        .SaveAs Filename:=FileNm, CreateBackup:=False
        Application.DisplayAlerts = True
        Application.Visible = True
        .Close Savechanges:=False
        MsgBox "Added to Mail Merge"
    End With
    Else
    MsgBox "Not added to mail merge!"
    End If
    TextBox1.Value = ""
    TextBox2.Value = "Enter"
    TextBox3.Value = "Details"
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox11.Value = ""
    TextBox12.Value = ""
    TextBox13.Value = ""
    TextBox14.Value = ""
    TextBox15.Value = ""
    ComboBox1.Value = "Select SalesID"

Exit Sub
End Sub

Thank you

Hi, excelloser,

maybe WorksheetFunction.Max will deliver the correct result if itīs numbers (you can add 1 to that for the next number). BTW: your code should be worked over thoroughly - I personally dislike Forms that let me do all the work and check at the very end when all has been filled out.

Ciao,
Holger