VBa code to subtotal by week ending date and monthly total

rjrsn

New Member
Joined
Feb 16, 2005
Messages
25
Hello, Help needed in a report. The data is listed in columns as indicated below. I need to generate a report showing an end of week subtotal ending on each Thursday at close of business. The fields that I need to generate by VBA are listed below the data. I need VBA to make each column and compile all data listed from Friday to the following Thursday and would be listed as week ending. The second week I need the same calculation based only on the second week, and then the 3rd and the 4th and/or 5th if it occurs. Once the weekly totals are calculated it goes into the weekly rows. Each week that ends in Thursday will have it's own total with the final row giving the monthly total. Can someone help me with the particular code to do this? Thanks for the anticipated help and feel free to ask any questions and I am able to privately email the particular document as necessary. Bob


LOCATION DATE "TRACE ID
NUMBER
(MMYY-NUMBER)" "BEGIN
TIME
(PULL DOWN)" "END
TIME
(PULL DOWN)" HOURS OFFICER "HMV
SPEED" HMV OTHER NHMV PARKING VW SPEED VW OTHER ARRESTS CRASHES CARS TOWED TOTAL
these are the fields where data is entered:

Subtotal lines are displayed as indicated below:

Week Ending HOURS "HMV
SPEED" HMV OTHER NHMV PARKING VW SPEED VW OTHER ARRESTS CRASHES CARS TOWED TOTAL
Thursday, November 9, 2017
Thursday, November 16, 2017
Thursday, November 23, 2017
Thursday, November 30, 2017
Totals for Month of (pulled from the week ending)
 
OK:
A8 = FIRST FRIDAY OF THE MONTH F8= Total of first week H8:Q8 = Total of all days in first week
A9 = 2nd FRIDAY OF THE MONTH F9= Total of first week H9:Q9 = Total of all days in first week
A10 = 3rd FRIDAY OF THE MONTH F10= Total of first week H10:Q10 = Total of all days in first week
A11 = 4th FRIDAY OF THE MONTH F11= Total of first week H11:Q11 = Total of all days in first week
A12 = 5th FRIDAY OF THE MONTH F12= Total of first week H12:Q12 = Total of all days in first week

A13 will have formula's in it to keep a running total of Month to date and doesn't need any code for that. There will be a SUM in this row to complete totals.
another question comes to mind. Is it possible include all the dates in the week in the weekly totals but any days of the week that don't occur in the same month to highlight and put in another cell so that they can stay added to the weekly totals but won't show in the month to date totals? That just came to mind... Thanks
 
Last edited:
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
My apologies for the time away I had a medical emergency. Notes to complete worksheet needed"
cell
a8 - week ending date to use for search criteria for first week - This will be manually entered and picked from a pull down reference to eliminate any bad data being entered. Each following cell in Column A will be a formula of a Previous cell + 7 to get the new ending dates for 9 - 12.
a9 - second week of month ending
a10 - third week of month ending
a11 - forth week of month ending
a12 - fifth week of month ending if it exists
a13 - month to date figures. ALL Data for the month of this report. Should not sum any numbers outside of the month being searched. ie exclude all data from overlapping week of previous month or next month.

Totals for each week:
row 8 in cell F8 total hours in the week - not month specific just in the range of each week date
row 9, 10, 11, 12 the same data for the corresponding week.
row 8 in cell H8 through Q8 the same data as above.

again row 13 will be a running total of all data searched during the month of this report skipping any numbers not in the specific date. For example data is week ending December 3. This would include all data captured for December 1 through 3 but not previous month. Again my apologies for the delay.
 
Upvote 0
Since I've had no more contact about this I'm guessing that I did something to anger someone. If that's the case never meant to and I understand no more work but could I get a copy of the code that has been worked on so I can play with it. Thanks Bov
 
Upvote 0

Forum statistics

Threads
1,213,582
Messages
6,114,471
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top