Date from entry sheet to roster sheet

[TreThacker]

Hello.
I am hoping my search query for this is correct and the answer I am looking for is not on this board yet. If it is, I apologize and request the link to that thread.

I am creating a workbook that will track disciplinary actions for an employee roster.

On a user-friendly Entry sheet, we use vlookup to pull various data from a Roster sheet for verification of the correct employee.
Once we can see the employee data, we would enter further data for that employee into multiple cells on the Entry sheet.
When we hit submit button, we want this data to be stored into specific cells back to the Roster sheet, corresponding to the employee found in the Roster sheet.

For example:
Roster sheet: Horizontal cell data: Badge: 6, Name: John Doe, Manager: Jeff, Clock In Time: (waits for data from Entry sheet), Clock Out Time (waits for data from Entry sheet), Reason: (waits for data from Entry sheet), Action: (waits for data from Entry sheet). The next 4 cells repeat the "waits for data" role for a second set of entries for the same person, same as a third set of 4 cells and a fourth set of 4 cells. So if an employee has 4 entries, all 4 would be shown on the same line as the employee info in this sheet. This horizontal data exists for a large number of employees, one per line down the Roster sheet.

Entry sheet: We type in the employee badge number and vlookup populates a table with the employee name and manager so we can verify we have the right employee. This data comes from the Roster sheet. Then we would enter the clock in and clock out times, the reason, and the action data into the Entry sheet and hit submit. This would place the newly entered data into a set of four cells that directly follow the employee data on the Roster sheet. If there is already data in the first 4 cells, it would populate this new data into the second 4 cells, and so on, keeping an ongoing record of up to 4 occurrences per employee.

Search sheet: This sheet allows us to type the badge ID of the employee and quickly access the data from up to 4 records we have previously stored per employee, which is all stored in the Roster sheet.

I have the sheets all working as described except for the submit button on the Entry sheet and copying the data to the proper cells on the Roster sheet. This is where I am needing help.

Thank you for your time and knowledge/expertise with this matter!

 

[dmt32]

Hi,
with such projects, helpful to the Forum if you can place a copy of your workbook with dummy data in a file sharing site like dropbox & provide a link to it here.

Dave

 

[TreThacker]

My apologies. Here is a link to a workbook with dummy data and notations on each sheet as to how they do and should work.

ToT Tracker - Dummy Data.xlsm

Thank you for any help!

 

[TreThacker]

Also apologies, I just realized there is a typo in the title. It should read "Data from entry sheet to roster sheet"

 

[dmt32]

Hi,
Place these two codes in a STANDARD module

Option Explicit
Sub Submit_Data()
    Dim wsEntry         As Worksheet, wsRoster As Worksheet
    Dim BadgeNo         As Variant, m As Variant
    Dim arr()           As Variant
    Dim Employee        As String
    Dim i               As Integer
    Dim rngEntry        As Range, rngRosta As Range, cell As Range
    
    Set wsEntry = ActiveSheet
    Set wsRoster = Worksheets("Roster")
    
    BadgeNo = wsEntry.Range("B6")
    If Len(BadgeNo) = 0 Then Exit Sub
    Employee = wsEntry.Range("D6")
    
    Set rngEntry = wsEntry.Range("B9:E9,G9:H9")
   
    ReDim arr(1 To rngEntry.Cells.Count)
    For Each cell In rngEntry.Cells: i = i + 1: arr(i) = cell.Value: Next cell
        
    m = Application.Match(BadgeNo, wsRoster.Columns(1), 0)
        
    If Not IsError(m) Then
    
        Set rngRosta = GetRostaRange(wsRoster, CLng(m))
            
        If Not rngRosta Is Nothing Then
            rngRosta.Resize(, UBound(arr)).Value = arr
            MsgBox Employee & Chr(10) & "Record Added To Roster", 64, "Record Added"
            Clearcells
        End If
            
    Else
            
        MsgBox BadgeNo & Chr(10) & "Record Not Found", 48, "Not Found"
            
    End If
End Sub

Function GetRostaRange(ByVal sh As Object, ByVal RecordRow As Long) As Range
    Dim r       As Long
    r = RecordRow
    Set GetRostaRange = sh.Range("H" & r & ", O" & r & ", V" & r & ", AC" & r).Find("", after:=sh.Cells(r, "AC"), lookat:=xlWhole)
    If GetRostaRange Is Nothing Then MsgBox "data is full and new data cannot be saved to Roster sheet", 48, "Data Full"
End Function

Assign your Submit Data shape object to Submit_Data code & see if does what you want.

Dave

 

[TreThacker]

This works perfectly! Thank you so much for the help!

 

[dmt32]

This works perfectly! Thank you so much for the help!

welcome - appreciate the feedback

Dave