VBa code to subtotal by week ending date and monthly total

[rjrsn]

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)

 

[oldbrewer]

can you put it in a spreadsheet layout as it is unclear ie is location .. date... traceidnumber...begintime...endtime...hoursofficer....hmvspeed....etc etc

 

[rjrsn]

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)

Came up with a simpler way I think. I have repeating data in rows 1 through 6 consisting of letterhead and a jpg picture.
A7-LOCATION
B7-DATE
C7-"TRACE ID NUMBER (MMYY-NUMBER)"
D7- "BEGIN TIME (PULL DOWN)"
E7-"END TIME (PULL DOWN)"
F7-HOURS(calculated field)
G7-OFFICER
H7-"HMV SPEED"
I7-HMV OTHER
J7-NHMV
K7-PARKING
L7-VW SPEED
M7-VW OTHER
N7-ARRESTS
O7-CRASHES
P7-CARS TOWED
Q7-TOTAL --Calculated formula SUM
Data is entered one event per row continuing down the worksheet.

In cells AB7 THROUGH AT13 is a range of cells showing weekly totals. 1st step of macro is to copy this range and insert it into cell A7 and also move the existing data down to row 14. So it should be insert copied data into the row and not erase anything. I say this because I don't want existing data overwritten just moved down.
Ok we have the new range inserted and all data is intact. Once this range is copied and inserted, cell A8 requires a date. I would like the macro to search the data in column B, starting at row 14 for the first Friday in the month of the dates in column B. example: date is shown as 11/14/2017. The macro reads the date as November and puts the date of the first friday in the month of November into cell A8.The macro should use this date to do a search of column B and search for any date from that Friday back to the previous Saturday Friday - 6 . Then search for all of these dates and sort chronologically in column B.
The macro should do a subtotal (weekly totals) on columns C, (this column has multiple numbers in it and I need a count of all non-blank cells during this time), F, and H through P, with a total of this row in column Q of the inserted range in row 8. Now the Macro inserts a blank line after the first week of data in the data.
the next Row 9, the macro would repeat itself but with the next week and data put into for the second weeks subtotals and again insert a blank line at the end of the second weeks data.
I couldn't upload a picture of what Im talking about, sorry. I hope this isn't too big of a job but I'm at a loss on how to make this work. It is for a traffic unit in a police department where I am a stat keeper. Any questions please ask and I hope I can be helped. thanks in advance.

 

[oldbrewer]

col Q

london

01/10/2017

1017-1001

10:35

13:59

03:24

Dibble

73

other1

something1

no

66

vwother1

2

0

1

london

01/10/2017

1017-1002

06:14

09:44

03:30

Jones

84

other2

something2

no

64

vwother2

1

1

1

is this the data

put in any altrerations

say what the total column actually totals

then I will add weekly and monthly totals

<colgroup><col><col><col span="7"><col><col span="8"></colgroup><tbody>
</tbody>

 

[rjrsn]

Hours in column is calculated and formatted as: [h]:mm;@ in the choices. I must ask that this would be the proper formatting for the hours to calculate and post such as 47:45 which would be 47 hours and 45 minutes. Just to make sure...

All data from H7 over to the right are whole numbers. They are listing the total number of violations and not the actual offense.
Q7 sums from H over to P and is formatted as a whole number.

Thanks Bob

 

[oldbrewer]

																col Q
london	29/09/2017	0917-1001	04:35	06:59	02:24	name1	73	5	7	9	11	13	4	6	8	136	row 7
london	30/09/2017	0917-1002	04:54	07:24	02:30	name2	74	8	9	11	13	15	6	8	10	154
london	01/10/2017	1017-1003	05:13	07:49	02:36	name3	75	11	11	13	15	17	8	10	12	172
london	02/10/2017	1017-1004	05:32	08:14	02:42	name4	76	14	13	15	17	19	10	12	14	190
london	03/10/2017	1017-1005	05:51	08:39	02:48	name5	77	17	15	17	19	21	12	14	16	208
london	04/10/2017	1017-1006	06:10	09:04	02:54	name6	78	20	17	19	21	23	14	16	18	226
london	05/10/2017	1017-1007	06:29	09:29	03:00	name7	79	23	19	21	23	25	16	18	20	244
london	06/10/2017	1017-1008	06:48	09:54	03:06	name8	80	26	21	23	25	27	18	20	22	262
london	07/10/2017	1017-1009	07:07	10:19	03:12	name1	81	29	23	25	27	29	20	22	24	280
london	08/10/2017	1017-1010	07:26	10:44	03:18	name2	82	32	25	27	29	31	22	24	26	298
london	09/10/2017	1017-1011	07:45	11:09	03:24	name3	83	35	27	29	31	33	24	26	28	316
london	10/10/2017	1017-1012	08:04	11:34	03:30	name4	84	38	29	31	33	35	26	28	30	334
london	11/10/2017	1017-1013	08:23	11:59	03:36	name5	85	41	31	33	35	37	28	30	32	352
london	12/10/2017	1017-1014	08:42	12:24	03:42	name6	86	44	33	35	37	39	30	32	34	370
london	13/10/2017	1017-1015	09:01	12:49	03:48	name7	87	47	35	37	39	41	32	34	36	388
london	14/10/2017	1017-1016	09:20	13:14	03:54	name8	88	50	37	39	41	43	34	36	38	406
london	15/10/2017	1017-1017	09:39	13:39	04:00	name1	89	53	39	41	43	45	36	38	40	424
london	16/10/2017	1017-1018	09:58	14:04	04:06	name2	90	56	41	43	45	47	38	40	42	442
london	17/10/2017	1017-1019	10:17	14:29	04:12	name3	91	59	43	45	47	49	40	42	44	460
london	18/10/2017	1017-1020	10:36	14:54	04:18	name4	92	62	45	47	49	51	42	44	46	478
london	19/10/2017	1017-1021	10:55	15:19	04:24	name5	93	65	47	49	51	53	44	46	48	496
london	20/10/2017	1017-1022	11:14	15:44	04:30	name6	94	68	49	51	53	55	46	48	50	514
london	21/10/2017	1017-1023	11:33	16:09	04:36	name7	95	71	51	53	55	57	48	50	52	532
london	22/10/2017	1017-1024	11:52	16:34	04:42	name8	96	74	53	55	57	59	50	52	54	550
london	23/10/2017	1017-1025	12:11	16:59	04:48	name1	97	77	55	57	59	61	52	54	56	568
london	24/10/2017	1017-1026	12:30	17:24	04:54	name2	98	80	57	59	61	63	54	56	58	586
london	25/10/2017	1017-1027	12:49	17:49	05:00	name3	99	83	59	61	63	65	56	58	60	604
london	26/10/2017	1017-1028	13:08	18:14	05:06	name4	100	86	61	63	65	67	58	60	62	622
london	27/10/2017	1017-1029	13:27	18:39	05:12	name5	101	89	63	65	67	69	60	62	64	640
london	28/10/2017	1017-1030	13:46	19:04	05:18	name6	102	92	65	67	69	71	62	64	66	658
london	29/10/2017	1017-1031	14:05	19:29	05:24	name7	103	95	67	69	71	73	64	66	68	676
london	30/10/2017	1017-1032	14:24	19:54	05:30	name8	104	98	69	71	73	75	66	68	70	694
london	31/10/2017	1017-1033	14:43	20:19	05:36	name1	105	101	71	73	75	77	68	70	72	712
london	01/11/2017	1117-1034	15:02	20:44	05:42	name2	106	104	73	75	77	79	70	72	74	730
london	02/11/2017	1117-1035	15:21	21:09	05:48	name3	107	107	75	77	79	81	72	74	76	748
london	03/11/2017	1117-1036	15:40	21:34	05:54	name4	108	110	77	79	81	83	74	76	78	766
london	04/11/2017	1117-1037	15:59	21:59	06:00	name5	109	113	79	81	83	85	76	78	80	784
london	05/11/2017	1117-1038	16:18	22:24	06:06	name6	110	116	81	83	85	87	78	80	82	802
london	06/11/2017	1117-1039	16:37	22:49	06:12	name7	111	119	83	85	87	89	80	82	84	820
london	07/11/2017	1117-1040	16:56	23:14	06:18	name8	112	122	85	87	89	91	82	84	86	838
	FRIDAYS	col Q
	29/09/2017	1330			one step at a time
	06/10/2017	2212
	13/10/2017	3094			by listing the dates of the Fridays easy to total for weeks ending on Thursdays
	20/10/2017	3976
	27/10/2017	4858
	03/11/2017	4010
	10/11/2017	0
	17/11/2017	0
	24/11/2017	0

<colgroup><col><col><col span="7"><col><col span="8"></colgroup><tbody>
</tbody>

 

[rjrsn]

This is looking great. Have the totals in column Q but when compiling that also need totals for the weekly stats for columns F, H through Q and will be inserted at the beginning of the data with the date listed as the Friday's date. I think I misspoke and the subtotals of the data by the week should end on the friday entered to search for and go back to the previous Saturday. The data week is always Saturday through Friday. I realize your sorted and subtotaled data can be moved to the location that I will ultimately assign it when I put a specific template into a specific set of cells.
When the totals/subtotals are calculated can a blank row be inserted into the data to seperate one week from the next and as each week is calculated a blank row would be inserted? When inserting that blank row can we color it a medium grey where not information would be there?

This is looking so great, thank you.

 

[oldbrewer]

																col Q			helper
london	30/09/2017	0917-1001	04:35	06:59	02:24	name1	73	5	7	9	11	13	4	6	8	136			1
london	30/09/2017		04:54	07:24	02:30	name2	74	8	9	11	13	15	6	8	10	154			0
london	01/10/2017	1017-1003	05:13	07:49	02:36	name3	75	11	11	13	15	17	8	10	12	172			1
london	02/10/2017		05:32	08:14	02:42	name4	76	14	13	15	17	19	10	12	14	190			0
london	03/10/2017	1017-1005	05:51	08:39	02:48	name5	77	17	15	17	19	21	12	14	16	208			1
london	04/10/2017	1017-1006	06:10	09:04	02:54	name6	78	20	17	19	21	23	14	16	18	226			1
london	05/10/2017	1017-1007	06:29	09:29	03:00	name7	79	23	19	21	23	25	16	18	20	244			1
london	06/10/2017	1017-1008	06:48	09:54	03:06	name8	80	26	21	23	25	27	18	20	22	262			1
london	07/10/2017	1017-1009	07:07	10:19	03:12	name1	81	29	23	25	27	29	20	22	24	280			1
london	08/10/2017	1017-1010	07:26	10:44	03:18	name2	82	32	25	27	29	31	22	24	26	298			1
london	09/10/2017	1017-1011	07:45	11:09	03:24	name3	83	35	27	29	31	33	24	26	28	316			1
london	10/10/2017	1017-1012	08:04	11:34	03:30	name4	84	38	29	31	33	35	26	28	30	334			1
london	11/10/2017	1017-1013	08:23	11:59	03:36	name5	85	41	31	33	35	37	28	30	32	352			1
london	12/10/2017	1017-1014	08:42	12:24	03:42	name6	86	44	33	35	37	39	30	32	34	370			1
london	13/10/2017	1017-1015	09:01	12:49	03:48	name7	87	47	35	37	39	41	32	34	36	388			1
london	14/10/2017	1017-1016	09:20	13:14	03:54	name8	88	50	37	39	41	43	34	36	38	406			1
london	15/10/2017	1017-1017	09:39	13:39	04:00	name1	89	53	39	41	43	45	36	38	40	424			1
london	16/10/2017	1017-1018	09:58	14:04	04:06	name2	90	56	41	43	45	47	38	40	42	442			1
london	17/10/2017	1017-1019	10:17	14:29	04:12	name3	91	59	43	45	47	49	40	42	44	460			1
london	18/10/2017	1017-1020	10:36	14:54	04:18	name4	92	62	45	47	49	51	42	44	46	478			1
london	19/10/2017	1017-1021	10:55	15:19	04:24	name5	93	65	47	49	51	53	44	46	48	496			1
london	20/10/2017	1017-1022	11:14	15:44	04:30	name6	94	68	49	51	53	55	46	48	50	514			1
london	21/10/2017	1017-1023	11:33	16:09	04:36	name7	95	71	51	53	55	57	48	50	52	532			1
london	22/10/2017	1017-1024	11:52	16:34	04:42	name8	96	74	53	55	57	59	50	52	54	550			1
london	23/10/2017	1017-1025	12:11	16:59	04:48	name1	97	77	55	57	59	61	52	54	56	568			1
london	24/10/2017	1017-1026	12:30	17:24	04:54	name2	98	80	57	59	61	63	54	56	58	586			1
london	25/10/2017	1017-1027	12:49	17:49	05:00	name3	99	83	59	61	63	65	56	58	60	604			1
london	26/10/2017	1017-1028	13:08	18:14	05:06	name4	100	86	61	63	65	67	58	60	62	622			1
london	27/10/2017	1017-1029	13:27	18:39	05:12	name5	101	89	63	65	67	69	60	62	64	640			1
london	28/10/2017	1017-1030	13:46	19:04	05:18	name6	102	92	65	67	69	71	62	64	66	658			1
london	29/10/2017	1017-1031	14:05	19:29	05:24	name7	103	95	67	69	71	73	64	66	68	676			1
london	30/10/2017	1017-1032	14:24	19:54	05:30	name8	104	98	69	71	73	75	66	68	70	694			1
london	31/10/2017	1017-1033	14:43	20:19	05:36	name1	105	101	71	73	75	77	68	70	72	712			1
london	01/11/2017	1117-1034	15:02	20:44	05:42	name2	106	104	73	75	77	79	70	72	74	730			1
london	02/11/2017	1117-1035	15:21	21:09	05:48	name3	107	107	75	77	79	81	72	74	76	748			1
london	03/11/2017	1117-1036	15:40	21:34	05:54	name4	108	110	77	79	81	83	74	76	78	766			1
london	04/11/2017	1117-1037	15:59	21:59	06:00	name5	109	113	79	81	83	85	76	78	80	784			1
london	05/11/2017	1117-1038	16:18	22:24	06:06	name6	110	116	81	83	85	87	78	80	82	802			1
london	06/11/2017	1117-1039	16:37	22:49	06:12	name7	111	119	83	85	87	89	80	82	84	820			1
london	07/11/2017	1117-1040	16:56	23:14	06:18	name8	112	122	85	87	89	91	82	84	86	838			1
	SATURDAYS	col Q	col F	col H	col I	col J	col K	col L	col M	col N	col O	col P		col C count<>""
	30/09/2017	1592	22:00	612	124	112	128	144	160	88	104	120		6
	07/10/2017	2338	00:30	588	266	203	217	231	245	182	196	210		7
	14/10/2017	3220	05:24	637	413	301	315	329	343	280	294	308		7
	21/10/2017	4102	10:18	686	560	399	413	427	441	378	392	406		7
	28/10/2017	4984	15:12	735	707	497	511	525	539	476	490	504		7
	04/11/2017	3244	00:36	442	470	328	336	344	352	316	324	332		4
	11/11/2017	0	00:00	0	0	0	0	0	0	0	0	0		0
	18/11/2017	0	00:00	0	0	0	0	0	0	0	0	0		0
	25/11/2017	0	00:00	0	0	0	0	0	0	0	0	0		0
		I am working on col F as when hours total over 24 they reset to zero
		week is now sat to Friday
		for testing purposes I deleted a couple of col C id's

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="5"></colgroup><tbody>
</tbody>

 

[oldbrewer]

the data can be in any order so not sure how and why you want rows moved and spaces inserted..........

 

[rjrsn]

This is looking so great. The hours used to reset for me too but when I formated the time to be [h]:mm:ss;@ it seemed to correct it. I did eliminate the "ss" part because I didn't need seconds.

When the report is presented to my superior's I will provide them with the weekly summary which will be the pasted part of the form. All these calculations occur there. The addition of a blank line after each week was subtotaled helps clarify the division between each week and was something the Major wanted me to do. If doable great if not no prob. To address your question about moving the subtotals into the rows in looking back at it I don't. I will keep the subtotals always displayed where they were going to be pasted. So the rows that I was going to insert will already have the formatting inserted and no need to copy from another part of the sheet. I didn't think that one clearly enough. The blank column will be helpful to look at the weeks for specific data and see where it ends. I will make a new note to you confirming the exact locations and such that each of the subtotaled data goes into. I'll post soon. thanks so much