Need VBA to update new emp through userform to sheet and table both

You mention "userform" but if you aleady have one, why is it not in your w/book?

A non_VBA example (but not what you want I suspect!)

in C3

Formula:

=IFERROR(VLOOKUP(INDEX(TableX!$C$3:$I$18,MATCH(Data!$B3,TableX!$B$3:$B$18,0),MATCH(Data!C$2,TableX!$C$2:$I$2,0)),Shift_Times,2,0),VLOOKUP(INDEX(TableX!$C$3:$I$18,MATCH(Data!$B3,TableX!$B$3:$B$18,0)+1,MATCH(Data!C$2,TableX!$C$2:$I$2,0)),Short_Term_Leave,2,0))

with named ranges for shifts / leave and new layout in "Tablex": no colour coding for "Leave" types

Having spare rows in Excel tables is pointless and can make solutions more complex.

And there is no obvious reason to separate out Shifts /Leave on separate rows .

Option Explicit
Sub Fill_Form()
Dim a, rng As Range, scode As String, empNo As Integer, shift As String
Dim i As Long, j As Long, lr As Long

Application.ScreenUpdating = False

With Sheets("Data")

    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = .Range("C2:I2")
    .Range("C3:K" & lr + 1).Clear
    
    For i = 3 To lr
        
        For j = 3 To 8
            empNo = Application.Match(.Cells(i, "B"), Range("Tablex").Columns(2), 0)
            scode = Application.Index(Range("Tablex"), empNo, j)
            If scode <> "" Then
                 shift = Application.VLookup(scode, Range("Shift_Times"), 2, 0)
                 .Cells(i, "J") = .Cells(i, "J") + 1
                 .Cells(lr + 1, "J") = .Cells(lr + 1, "J") + 1
            Else
                empNo = empNo + 1
                scode = Application.Index(Range("Tablex"), empNo, j)
                shift = Application.VLookup(scode, Range("Short_term_leave"), 2, 0)
                .Cells(i, j).Interior.ColorIndex = Application.VLookup(scode, Range("Short_term_leave"), 3, 0)
                .Cells(i, "K") = .Cells(i, "K") + 1
                .Cells(lr + 1, "K") = .Cells(lr + 1, "K") + 1
            End If
            .Cells(i, j) = shift
        Next j
        
    Next i
    
    With .Range("A1:K" & lr + 1)
        .Borders.Weight = 2
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    
End With

Application.ScreenUpdating = True

End Sub

Yes it could be changed but NOT with the current "TABLE format which is why I changed it in my solution : your layout is "pretty" but not "practical",

Input errors: there are entries in both employee lines for a given date so you need to address this. The easiest way is to have a SINGLE line per employee with a drop-down which has both Shifts+Leave in the list.

And what about the "Totals" which were in your original post? Better to be positioned in columns C:D followed by the dates in E onwards..

I await your response to these points before I do further work.

Option Explicit
Sub Fill_Form()

Dim a, rng As Range, tabRng As Range, scode As String, EmpNo_Row As Integer, description As String
Dim i As Long, j As Long, lr As Long, lc As Integer

Application.ScreenUpdating = False

Set tabRng = Sheets("Table").Range("$A$1:$AG$200")  ' Table for upto 31 days

With Sheets("Data")

    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    lc = .Cells(2, Columns.Count).End(xlToLeft).Column
    Set rng = .Range(.Cells(3, 2), .Cells(lr, lc))
    .Range(.Cells(3, 3), .Cells(lr + 1, lc)).Clear                                       ' Clear output in "Data"
    
    For i = 3 To lr                                                                      ' Loop through EMPLOYEES
        
        For j = 3 To lc - 2                                                              ' Loop through month days
          
            EmpNo_Row = Application.Match(.Cells(i, "B"), Range("Employee_Name"), 0)     ' Get row for this Employee
            scode = Application.Index(tabRng, EmpNo_Row, j)                              ' Get SHIFT Code
            description = ""
            
            If scode <> "" Then
                 description = Application.VLookup(scode, Range("Shift_Times"), 2, 0)          ' Get SHIFT description
                 .Cells(i, "AH") = .Cells(i, "AH") + 1                                   ' Count of EMPLOYEE Working days
                 .Cells(lr + 1, "AH") = .Cells(lr + 1, "AH") + 1                         ' Count of TOTAL Working days
            Else
                EmpNo_Row = EmpNo_Row + 1                                                ' Get second row of this Employee
                scode = Application.Index(tabRng, EmpNo_Row, j)                          ' get LEAVE code
                If scode <> "" Then
                    description = Application.VLookup(scode, Range("Short_term_leave"), 2, 0)  'get LEAVE description
                    .Cells(i, j).Interior.ColorIndex = Application.VLookup(scode, Range("Short_term_leave"), 3, 0) ' Highlight Cell
                    .Cells(i, "AI") = .Cells(i, "AI") + 1                                ' Count of Employee LEAVE days
                    .Cells(lr + 1, "AI") = .Cells(lr + 1, "AI") + 1                      ' Count of TOTAL Leave days
                End If
            End If
            
            .Cells(i, j) = description                                                         'Shift/Leave descriptioon
        Next j
        
    Next i
    
    With .Range("A1:AI" & lr + 1)
        .Borders.Weight = 2
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    
End With

Application.ScreenUpdating = True

End Sub

I wasted my time on a VBA solution so no more from me!!