Sum of multiple worksheets 1

[ALEKSJAY]

Hi People,
I have a spreadsheet with multiple worksheets. One worksheet for each employee. In those worksheets, I have columns for Department, Week1,Week2,Week3, and Week4. I record in each row the hours worked in each department. I have another worksheet with th same rows and columns, where I sum all the other worksheets. My problem is that each time I have to add a worksheet, I have to edit the "Totals" worksheet to reflect this new employee. Any sugestions on how do simplify this?

 

[rmikesmith]

Aleksjay,

I am assuming that you are currently using worksheet formulas to generate the total hours that appear in the summary sheet. I have put together a different approach that uses some VBA coding to generate the totals. It is flexible enough to include any new employee worksheet when calculating the total hours.

For my example, I assumed the number of departments are static and that each worksheet, including the totals sheet, are layed out identically (actually, the cells containing headings, departments, and hours just need to be the same). On the Totals worksheet, I set up a named range (TotalHours) covering the block of cells where total hours appear. This allows easy reference in the code to be able to delete the old values.

Here is the procedure to update the Totals worksheet:

Sub UpdateTotalHours()
Dim wks As Worksheet
Dim wksTotals As Worksheet
Dim i As Long
Dim j As Long

  Application.ScreenUpdating = False
  Set wksTotals = ThisWorkbook.Worksheets("Totals")
  wksTotals.Range("TotalHours").ClearContents
  
  For Each wks In ThisWorkbook.Worksheets
    If wks.Name <> &quot;Totals&quot; Then
      For i = 2 To 5
        For j = 2 To 5
          wksTotals.Cells(i, j).Value = wksTotals.Cells(i, j).Value + _
          wks.Cells(i, j).Value
        Next j
      Next i
    End If
  Next wks
  Application.ScreenUpdating = True
End Sub

I added a commandbutton on the Totals sheet that runs the above procedure. If you would like a copy of the demo workbook, post your email address.

HTH
Mike

 

[ALEKSJAY]

Thank you, rmikesmith,
It worked perfect...