Sum Data in a Dynamic Range

[txaggie96]

I have a spreadsheet with employee names, jobs, and their work hours per month, as shown below. I pull this data from a large database and I use VBA code get it into this form.

Name Job Apr May Jun
Bill Job1 10 10 10
Bill
Joe Job1 20 10 20
Joe Job2 10 10 0
Joe Job3 0 5 5
Joe
John Job1 20 10 10
John Job2 10 0 0
John

and so on...

Employees can have one or more jobs and it changes monthly. I would like to be able to sum the hours per month and put the totals on the blank row under the last job listed. Does that make sense? I have stared at this for awhile and just can't figure out a way to do it. I cannot use formulas in the cells. I would need to use VBA code as the sheet gets cleared every month so new data can go on it. Any help would be appreciated. Thanks.

 

[scottylad2]

is there a maximum number of jobs per month?

 

[seenfresh]

Why not have a data dump as you have done in one sheet and create a dynamic named range, or table for this data. In another sheet create a pivot table that references this data and every time you update your data sheet just refresh your pivot table to update?

 

[MickG]

Hi, Try this:-
The Total Hours for each Persons jobs will be shown in the Blank column "B" cell against their name.
The code when rerun, will overwrite any Numerical data in that cell from a previous code run.

Sub tots()
Dim Last As Long, RngAc As Range, Tot As Integer, SumTot As Long
Dim Temp As String
Last = Range("A" & Rows.Count).End(xlUp).Row
 For Tot = 1 To Last
            Temp = Cells(Tot, "A")
        Do While Cells(Tot, "A") = Temp
            Set RngAc = Range(Range("C" & Tot), Cells(Tot, Columns.Count).End(xlToLeft))
                    If IsNumeric(Cells(Tot, "B")) Or Cells(Tot, "B") = "" Then Cells(Tot, "B") = SumTot
                        SumTot = SumTot + Application.Sum(RngAc)
                            Tot = Tot + 1
        Loop
     Tot = Tot - 1
    SumTot = 0
Next Tot
End Sub

Regards Mick