code to record an amount into a table

You're welcome and thanks for the rep!

Maybe:

Private Sub cmdOK_Click()
    Dim RowCount As Long
    Dim ctl As Control
    Dim ws As Worksheet, N As String, Z, r As Long, S As Single
    N = Me.cboDepartment.Value: Z = Split(N): N = Z(0)
    For Each ws In Worksheets
    If ws.Name = N Then GoTo Setws
    Next
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = N
    Sheets("Lista").Rows(1).Copy Sheets(N).Range("A1")
    
Setws:    Set ws = Sheets(N)
' Write data to worksheet
    RowCount = ws.Range("A1").CurrentRegion.Rows.Count
    With ws.Range("A1")
        .Offset(RowCount, 0).Value = Me.cboDepartment.Value
        .Offset(RowCount, 1).Value = Me.txtAmount.Value
        .Offset(RowCount, 2).Value = Format(Now, "dd/mm/yyyy")
        For r = 2 To RowCount + 1: S = S + CSng(ws.Range("B" & r)): Next r
        .Offset(RowCount, 4).Value = S: S = 0
        .Offset(RowCount, 5).Value = .Offset(RowCount, 2).Value

    End With
' Clear the form
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        ElseIf TypeName(ctl) = "CheckBox" Then
            ctl.Value = False
        End If
    Next ctl
End Sub