Sum values and insert new row based on conditions

[swkbcf23]

Hello all,

I have the following table of data that I would like to transform with a macro. Overall I'd like to sum the values in column F by date (column A). And insert a new line with that total after the last summed value for each date.

Due Date	Prod. Order No.	IteP No.	Description	Quantity	Hours
9/11/2023	P101899	100000	Fake Sku 1	732	1.22
9/11/2023	P101900	100001	Fake Sku 2	408	0.76
9/11/2023	P101901	100002	Fake Sku 3	3,300	6.11
9/11/2023	P101902	100003	Fake Sku 4	5,398	5.62
9/11/2023	P101903	X100004	Fake Sku 5	9,900
9/11/2023	P101904	X100005	Fake Sku 6	450
9/12/2023	P101905	100006	Fake Sku 1	300	0.56
9/12/2023	P101906	100007	Fake Sku 2	2,370	4.39
9/12/2023	P101907	100008	Fake Sku 3	2,511	2.62
9/12/2023	P101908	100009	Fake Sku 4	3,024	3.15
9/12/2023	P101909	100010	Fake Sku 5	161	0.67
9/12/2023	P101910	X100011	Fake Sku 6	6,750
9/12/2023	P101911	X100012	Fake Sku 7	3,600
9/13/2023	P101912	100013	Fake Sku 10	2,805	5.19
9/13/2023	P101913	100014	Fake Sku 11	5,412	5.64
9/13/2023	P101914	100015	Fake Sku 12	25	0.1
9/13/2023	P101915	100016	Fake Sku 13	207	0.86
9/13/2023	P101916	X100017	Fake Sku 14	2,250
9/13/2023	P101917	X100018	Fake Sku 15	1,350
9/13/2023	P101918	X100019	Fake Sku 16	150
9/13/2023	P101919	X100020	Fake Sku 17	6,600

Again, I would like to transform that data by summing the values in column F by date in column A. I want to insert this sum result underneath the last instance of data in column F that is summed for each date. In otherwords, I'd like the new row to be inserted before the first instance of an item number (in Column C) starting with "X" for each date. I also want to add the date to the newly inserted row in column A and the word "Total" to column E. The result would look like this:

Due Date	Prod. Order No.	IteP No.	Description	Quantity	Hours
9/11/2023	P101899	100000	Fake Sku 1	732	1.22
9/11/2023	P101900	100001	Fake Sku 2	408	0.76
9/11/2023	P101901	100002	Fake Sku 3	3,300	6.11
9/11/2023	P101902	100003	Fake Sku 4	5,398	5.62
9/11/2023				Total	13.71
9/11/2023	P101903	X100004	Fake Sku 5	9,900
9/11/2023	P101904	X100005	Fake Sku 6	450
9/12/2023	P101905	100006	Fake Sku 1	300	0.56
9/12/2023	P101906	100007	Fake Sku 2	2,370	4.39
9/12/2023	P101907	100008	Fake Sku 3	2,511	2.62
9/12/2023	P101908	100009	Fake Sku 4	3,024	3.15
9/12/2023	P101909	100010	Fake Sku 5	161	0.67
9/12/2023				Total	11.39
9/12/2023	P101910	X100011	Fake Sku 6	6,750
9/12/2023	P101911	X100012	Fake Sku 7	3,600
9/13/2023	P101912	100013	Fake Sku 10	2,805	5.19
9/13/2023	P101913	100014	Fake Sku 11	5,412	5.64
9/13/2023	P101914	100015	Fake Sku 12	25	0.1
9/13/2023	P101915	100016	Fake Sku 13	207	0.86
9/13/2023				Total	11.79
9/13/2023	P101916	X100017	Fake Sku 14	2,250
9/13/2023	P101917	X100018	Fake Sku 15	1,350
9/13/2023	P101918	X100019	Fake Sku 16	150
9/13/2023	P101919	X100020	Fake Sku 17	6,600

I have tried using the following code but it is not working correctly; it works for the first date but then just keeps inserting blank rows instead of going through the other data.

Sub InsertTotalRows()
    Dim LastRow As Long
    Dim CurrentDate As Date
    Dim TotalHours As Double
    Dim ws As Worksheet
    Dim i As Long
    
    
    Set ws = ActiveWorkbook.Sheets("Sheet1")
    
    ' Initialize variables
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    CurrentDate = ws.Cells(2, 1).Value
    TotalHours = 0
    
    ' Loop through rows
    For i = 2 To LastRow
        If ws.Cells(i, 1).Value = CurrentDate And ws.Cells(i, 6).Value <> "" Then
            ' Add the hours to the total
            TotalHours = TotalHours + ws.Cells(i, 6).Value
        Else
            ' Insert a new row and add the total
            ws.Rows(i).Insert Shift:=xlDown
            ws.Cells(i, 1).Value = CurrentDate
            ws.Cells(i, 5).Value = "Total"
            ws.Cells(i, 6).Value = TotalHours
            
            ' Reset variables
            CurrentDate = ws.Cells(i, 1).Value
            TotalHours = 0
        End If
    Next i
    
    ' Insert the last total row
    ws.Rows(LastRow + 1).Insert Shift:=xlDown
    ws.Cells(LastRow + 1, 1).Value = CurrentDate
    ws.Cells(LastRow + 1, 5).Value = "Total"
    ws.Cells(LastRow + 1, 6).Value = TotalHours
End Sub

Would anyone be able to help me achieve this and/or troubleshoot my existing code (also open to new code)? I thought it would be simple at first but it's proving to be above my abilities at the moment. Thank you.

 

[kevin9999]

Please try the following on a copy of your worksheet (change worksheet name to suit)

Option Explicit
Sub swkbcf23()
    Application.ScreenUpdating = False
    Const t As String = "Total"
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")      '<-- *** Change to actual sheet name ***
    Dim LRow As Long, i As Long
    LRow = ws.Cells(Rows.Count, "C").End(xlUp).Row
    For i = LRow To 2 Step -1
        If ws.Cells(i, 3) Like "X*" And Not ws.Cells(i - 1, 3) Like "X*" Then
            With ws.Rows(i)
                .EntireRow.Insert
                With .Offset(-1)
                    .Font.Bold = True
                    .Cells(1, 5) = t
                    .Cells(1, 6) = Application.Sum(Range(.Cells(1, 6), .Cells(1, 6).Offset(-1).End(xlUp)))
                End With
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Before:

Book1
	A	B	C	D	E	F
1	Due Date	Prod. Order No.	IteP No.	Description	Quantity	Hours
2	9/11/2023	P101899	100000	Fake Sku 1	732	1.22
3	9/11/2023	P101900	100001	Fake Sku 2	408	0.76
4	9/11/2023	P101901	100002	Fake Sku 3	3,300	6.11
5	9/11/2023	P101902	100003	Fake Sku 4	5,398	5.62
6	9/11/2023	P101903	X100004	Fake Sku 5	9,900
7	9/11/2023	P101904	X100005	Fake Sku 6	450
8
9	9/12/2023	P101905	100006	Fake Sku 1	300	0.56
10	9/12/2023	P101906	100007	Fake Sku 2	2,370	4.39
11	9/12/2023	P101907	100008	Fake Sku 3	2,511	2.62
12	9/12/2023	P101908	100009	Fake Sku 4	3,024	3.15
13	9/12/2023	P101909	100010	Fake Sku 5	161	0.67
14	9/12/2023	P101910	X100011	Fake Sku 6	6,750
15	9/12/2023	P101911	X100012	Fake Sku 7	3,600
16
17	9/13/2023	P101912	100013	Fake Sku 10	2,805	5.19
18	9/13/2023	P101913	100014	Fake Sku 11	5,412	5.64
19	9/13/2023	P101914	100015	Fake Sku 12	25	0.1
20	9/13/2023	P101915	100016	Fake Sku 13	207	0.86
21	9/13/2023	P101916	X100017	Fake Sku 14	2,250
22	9/13/2023	P101917	X100018	Fake Sku 15	1,350
23	9/13/2023	P101918	X100019	Fake Sku 16	150
24	9/13/2023	P101919	X100020	Fake Sku 17	6,600
Sheet1

After:

Book1
	A	B	C	D	E	F
1	Due Date	Prod. Order No.	IteP No.	Description	Quantity	Hours
2	9/11/2023	P101899	100000	Fake Sku 1	732	1.22
3	9/11/2023	P101900	100001	Fake Sku 2	408	0.76
4	9/11/2023	P101901	100002	Fake Sku 3	3,300	6.11
5	9/11/2023	P101902	100003	Fake Sku 4	5,398	5.62
6					Total	13.71
7	9/11/2023	P101903	X100004	Fake Sku 5	9,900
8	9/11/2023	P101904	X100005	Fake Sku 6	450
9
10	9/12/2023	P101905	100006	Fake Sku 1	300	0.56
11	9/12/2023	P101906	100007	Fake Sku 2	2,370	4.39
12	9/12/2023	P101907	100008	Fake Sku 3	2,511	2.62
13	9/12/2023	P101908	100009	Fake Sku 4	3,024	3.15
14	9/12/2023	P101909	100010	Fake Sku 5	161	0.67
15					Total	11.39
16	9/12/2023	P101910	X100011	Fake Sku 6	6,750
17	9/12/2023	P101911	X100012	Fake Sku 7	3,600
18
19	9/13/2023	P101912	100013	Fake Sku 10	2,805	5.19
20	9/13/2023	P101913	100014	Fake Sku 11	5,412	5.64
21	9/13/2023	P101914	100015	Fake Sku 12	25	0.1
22	9/13/2023	P101915	100016	Fake Sku 13	207	0.86
23					Total	11.79
24	9/13/2023	P101916	X100017	Fake Sku 14	2,250
25	9/13/2023	P101917	X100018	Fake Sku 15	1,350
26	9/13/2023	P101918	X100019	Fake Sku 16	150
27	9/13/2023	P101919	X100020	Fake Sku 17	6,600
Sheet1

 

[swkbcf23]

Thank you for the reply and code, that worked well! I appreciate your help!

 

[kevin9999]

Glad it worked & thanks for the feedback