VBA Macro to compare time sheet data and build report.

[manonfire32]

Hello Everyone,

I have time sheet data on sheet 1(week one report) and sheet 2(week two report) that contains
Sheet 1
Employee Name, Total Reg hours for the week, Total OT Hours for the week.
EG= John Smith, 15, , 5

Sheet2
Employee Name, Total Reg hours for the week, Total OT Hours for the week.
EG= John Smith, 10, , 2

Then on sheet 3 I need to output the combined time in a format as such,
Sheet3
Employee Name, Type of time, Total Reg or OT Hours for the week.
EG= John Smith, Regular, , 25
EG= John Smith, Over Time, , 7


Obviously my challenges are that the employees can change, and the hours can change.

I was thinking about using collections to collect the data and then output on sheet 3. I am new to VB and would love some help on this subject.

Thank you,
Steve

 

[b.downey]

The following code should get you started. It assumes that the workbook contains a Sheet called "Summary" that will conain all of the Summary results. Every time the Macro is executes, it will rebuilt the "Summary" will be redone

Option Explicit
Type TypeRec
    Name As String
    Reg As Long
    OT As Long
End Type

Sub ProcessTimeSheets()
    Const SummarySheetName As String = "Summary"
    Dim Wb As Workbook
    Dim Ws As Worksheet
    
    Dim WsSummary As Worksheet
    
    Dim SheetNo As Integer
    Dim RowNo As Long
    Dim Rec() As TypeRec
    Dim IDX As Long
    
    ReDim Rec(0)
    
    Set Wb = ThisWorkbook
    Set WsSummary = Wb.Sheets(SummarySheetName)
    For SheetNo = 1 To Wb.Sheets.Count
        If Wb.Sheets(SheetNo).Name <> SummarySheetName Then
            Set Ws = Wb.Sheets(SheetNo)
            For RowNo = 2 To Ws.Cells(Ws.Rows.Count, "A").End(xlUp).Row
                IDX = FindIDX(Rec, Ws.Cells(RowNo, "A"))
                Rec(IDX).Reg = Rec(IDX).Reg + Ws.Cells(RowNo, "B")
                Rec(IDX).OT = Rec(IDX).OT + Ws.Cells(RowNo, "D")
            Next RowNo
        End If
    Next SheetNo
    
    WsSummary.Cells.ClearContents
    For IDX = 1 To UBound(Rec)
        WsSummary.Cells(IDX + 1, "A") = Rec(IDX).Name
        WsSummary.Cells(IDX + 1, "B") = Rec(IDX).Reg
        WsSummary.Cells(IDX + 1, "D") = Rec(IDX).OT
    Next IDX
    WsSummary.Activate
End Sub