Adding up hours from separate sheets using VBA

[TAPS_MikeDion]

Hello everybody,
I'm hoping someone will be able to figure this out for me...please.

Here's what I need to have VBA do:

1. Scan through column D (ID numbers) in my Division Member Info (DMI) sheet
2. Scan through column G (ID numbers) in my monthly sheets (D-JAN, D-FEB, D-MAR, etc to D-DEC) and find the ID number matching the ID number in the DMI sheet column D
3. Individually add up the hours found for each ID number from column N in the monthly (D-JAN, D-FEB, etc) sheets
4. Put the final total of hours (per ID number) in column AG (Detail Hours) of the DMI sheet

I have a sample TestBook Excel file made to make things easier, but I'm not sure how to get it to anyone willing to help.

Thank you in advance!
-Mike

 

[DanteAmor]

Hi @TAPS_MikeDion. Thanks for posting on the board.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[TAPS_MikeDion]

Hi again Dante,
Thank you!
Okay, here's the Dropbox link. I hope it works:

TestBook

 

[TAPS_MikeDion]

I should add that I'm using Excel for Mac v16.40 and VBA v7.1

 

[DanteAmor]

Hi Mike:

Try this macro:

Sub sum_hours()
  Dim sh As Worksheet
  Dim c As Range
  Dim tot As Double
  
  With Sheets("Division Member Info")
    For Each c In .Range("D3", .Range("D" & Rows.Count).End(3))
      tot = 0
      For Each sh In Sheets
        If UCase(Left(sh.Name, 2)) = "D-" Then
          tot = tot + WorksheetFunction.SumIf(sh.Range("G:G"), c.Value, sh.Range("N:N"))
        End If
      Next
      .Range("AG" & c.Row).Value = tot
    Next
  End With
End Sub

Note: The format in the AG column must be [h]:mm to show the total hours:

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[TAPS_MikeDion]

Hi Dante,
I'm amazed that you were able to get that in such a short macro, awesome!!! You're the best!!!

I was informed that it will only be full hours, so I formatted the cells using just [h] and it works perfectly.

Here's the only issue I'm having now...
As you can see by the screenshot, the hours aren't adding correctly, which I'm assuming may have to do with formatting???

The TOTAL HOURS (column AI) should read:
Smith - 50
Jones - 53
Doe - 45

The REMAINING HOURS (column AJ) cells have the formula "=MAX(0,50-AI3)" and should read:
Smith - 0
Jones - 0
Doe - 5

Do you know what I would need to do to these add up correctly?

Thank you so much again!

 

[DanteAmor]

Hi Mike:

As you can see by the screenshot, the hours aren't adding correctly, which I'm assuming may have to do with formatting???

The TOTAL HOURS (column AI) should read:
Smith - 50
Jones - 53
Doe - 45

The REMAINING HOURS (column AJ) cells have the formula "=MAX(0,50-AI3)" and should read:
Smith - 0
Jones - 0
Doe - 5

That is the ultimate goal, you should mention it at the beginning of your request, that way I will work on a complete answer.
I explain:
3:00 hours, correspond to part of a day (24 hours), that is, 0.125 of a day. So to get the integer value of those hours, you must multiply by 24.

So this you must do in your sheet.
1) Change the format of the AG, AI and AJ columns to general format.
2) Use this macro, which already converts hours to integers.

Sub sum_hours()
  Dim sh As Worksheet
  Dim c As Range
  Dim tot As Double
  
  With Sheets("Division Member Info")
    For Each c In .Range("D3", .Range("D" & Rows.Count).End(3))
      tot = 0
      For Each sh In Sheets
        If UCase(Left(sh.Name, 2)) = "D-" Then
          tot = tot + WorksheetFunction.SumIf(sh.Range("G:G"), c.Value, sh.Range("N:N"))
        End If
      Next
      .Range("AG" & c.Row).Value = tot * 24
    Next
  End With
End Sub

After running the macro:

---------------------------------
Supplementary Note:
You need to update your file as you have too many formats and you use up to a million cells but they are empty.
I suggest the following, create a new workbook, in that workbook create new sheets and give them the same name as you have in the current workbook, copy only the cells with data from each sheet in the current workbook and paste it into the new worksheet inside the new book.
Save your new file.
Your file that you shared is 5 MB, after performing the above procedure the book was 27 KB for me.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[TAPS_MikeDion]

Hi Dante,
I'll try what you suggested and let you know. Unfortunately, I didn't realize this was going to be an issue or I would have mentioned it at the start of my post.

I'll also try your suggestion and see if I can drop the file size.

Thanks again for everything!

 

[TAPS_MikeDion]

Thanks again for everything Dante!

Also, your suggestion worked wonders for my file. After doing what you recommended I went from a 19 MB file to a 128 KB file!!!