VBA to Add 2 Rows and Text and Formula

[luke6843]

Iam not very good with VBA but I do take existing code and adapt it the best Ican to do what I need. I am now at aloss for this problem trying all I can to get it to work. I am using this code:<o></o>
<o> </o>

<o:p></o:p>

Subinsertem()<o:p></o:p>
Dimr As Long<o:p></o:p>
Forr = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1<o:p></o:p>
    If Range("L" & r).Value<> Range("L" & r - 1) Then<o:p></o:p>
        Rows(r).Insert<o:p></o:p>
    End If<o:p></o:p>
Nextr<o:p></o:p>
EndSub<o:p></o:p>

<o></o>
<o> </o>
toadd extra rows in between dates – see Exhibit 1. What I need it to do now is add 2 rowsinstead of 1. Then put the word “Total”in column A of the first 2 rows. Thenput a SUM formula in columns G, H and I totaling only up to the last blank row –see Exhibit 2.<o></o>
<o> </o>
Exhibit1<o></o>
<o>

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item Number	Description	Customer Code	Customer Name	SO Number	Qty Sched (Cases)	Widgets Total #	Screws Total #	Other	LABELS RAN	Defective	Due Date
2	607170	Widget 1		Customer 1	84952	100	2000			0		Tuesday, July 25, 2017
3	623641	Widget 2		Customer 2	85359	350			13990	0		Tuesday, July 25, 2017
4	300010	Widget 3		Customer 3	85361	10			200	0		Tuesday, July 25, 2017
5	300013	Widget 4		Customer 3	85361	10			200	0		Tuesday, July 25, 2017
6	605345	Widget 5		Customer 3	85361	50	1000			0		Tuesday, July 25, 2017
7	606241	Widget 6		Customer 3	85361	200	6000			0		Tuesday, July 25, 2017
8	610014	Widget 7		Customer 3	85361	100			2000	0		Tuesday, July 25, 2017
9	607140	Widget 8		Customer 4	85408	70	1400			0		Tuesday, July 25, 2017
10	607210	Widget 9		Customer 5	85408	20			400	0		Tuesday, July 25, 2017
11
12	150650	Widget 10		Cutomer 6	84173	1000			20000	0		Wednesday, July 26, 2017
13	600000	Widget 11		Customer 7	85098	189	3780			0		Wednesday, July 26, 2017
14	600006	Widget 12		Customer 7	85098	63	1260			0		Wednesday, July 26, 2017
15	1250	Widget 13		Customer 8	85167	312				0	7488	Wednesday, July 26, 2017
16	622414	Widget 14		Customer 9	85239	96			4992	0		Wednesday, July 26, 2017
17
18	600500	Widget 15		Customer 10	85164	150			3000	0		Thursday, July 27, 2017
19	600501	Widget 16		Customer 10	85164	150			3000	0		Thursday, July 27, 2017
20	602366	Widget 17		Customer 11	85493	87	1740			0		Thursday, July 27, 2017
21	624565	Widget 18		Customer 10	85502	100			1600	0		Thursday, July 27, 2017
22
23	606142	Widget 19		Customer 12	85281	56	1120			0		Monday, July 31, 2017
24	606143	Widget 20		Customer 12	85281	224	6720			0		Monday, July 31, 2017
25	606149	Widget 21		Customer 12	85281	56	1680			0		Monday, July 31, 2017
26
27	600000	Widget 11		Customer 7	85099	189	3780			0		Wednesday, August 02, 2017
28
29	606143	Widget 20		Customer 12	85265	112	3360			0		Thursday, August 03, 2017
30
31	600000	Widget 11		Customer 7	85100	189	3780			0		Wednesday, August 09, 2017
32	600006	Widget 12		Customer 7	85100	63	1260			0		Wednesday, August 09, 2017
33
34	606142	Widget 19		Customer 12	85266	6	120			0		Thursday, August 10, 2017
35	606143	Widget 20		Customer 12	85266	106	3180			0		Thursday, August 10, 2017
36
Sheet1

</o>
<o></o>
<o> </o>
Exhibit2

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item Number	Description	Customer Code	Customer Name	SO Number	Qty Sched (Cases)	Widgets Total #	Screws Total #	Other	LABELS RAN	Defective	Due Date
2	607170	Widget 1		Customer 1	84952	100	2000			0		Tuesday, July 25, 2017
3	623641	Widget 2		Customer 2	85359	350			13990	0		Tuesday, July 25, 2017
4	300010	Widget 3		Customer 3	85361	10			200	0		Tuesday, July 25, 2017
5	300013	Widget 4		Customer 3	85361	10			200	0		Tuesday, July 25, 2017
6	605345	Widget 5		Customer 3	85361	50	1000			0		Tuesday, July 25, 2017
7	606241	Widget 6		Customer 3	85361	200	6000			0		Tuesday, July 25, 2017
8	610014	Widget 7		Customer 3	85361	100			2000	0		Tuesday, July 25, 2017
9	607140	Widget 8		Customer 4	85408	70	1400			0		Tuesday, July 25, 2017
10	607210	Widget 9		Customer 5	85408	20			400	0		Tuesday, July 25, 2017
11	Total					910	10400	0	16790
12
13	150650	Widget 10		Cutomer 6	84173	1000			20000	0		Wednesday, July 26, 2017
14	600000	Widget 11		Customer 7	85098	189	3780			0		Wednesday, July 26, 2017
15	600006	Widget 12		Customer 7	85098	63	1260			0		Wednesday, July 26, 2017
16	1250	Widget 13		Customer 8	85167	312				0	7488	Wednesday, July 26, 2017
17	622414	Widget 14		Customer 9	85239	96			4992	0		Wednesday, July 26, 2017
18	Total					1660	5040	0	24992
19
20	600500	Widget 15		Customer 10	85164	150			3000	0		Thursday, July 27, 2017
21	600501	Widget 16		Customer 10	85164	150			3000	0		Thursday, July 27, 2017
22	602366	Widget 17		Customer 11	85493	87	1740			0		Thursday, July 27, 2017
23	624565	Widget 18		Customer 10	85502	100			1600	0		Thursday, July 27, 2017
24	Total					487	1740	0	7600
25
26	606142	Widget 19		Customer 12	85281	56	1120			0		Monday, July 31, 2017
27	606143	Widget 20		Customer 12	85281	224	6720			0		Monday, July 31, 2017
28	606149	Widget 21		Customer 12	85281	56	1680			0		Monday, July 31, 2017
29	Total					336	9520	0	0
30
31	600000	Widget 11		Customer 7	85099	189	3780			0		Wednesday, August 02, 2017
32	Total					189	3780	0	0
33
Sheet2

Any help or suggestions would be greatly appreciated.
Thanks
Luke

 

[onlyadrafter]

Hello,

does this work as expected?

Sub insertem1()
Dim r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 3 Step -1
If Range("L" & r).Value <> Range("L" & r - 1) Then
Rows(r).Insert
Rows(r).Insert
End If
Next r
For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row + 1
    If IsEmpty(Range("a" & r).Value) Then
        Range("A" & r).Value = "Total"
        MY_END = Range("A" & r - 1).Row
        MY_START = Range("A" & r - 1).End(xlUp).Row
        If MY_START = 1 Then MY_START = 2
        Range("F" & r).Formula = "=sum(F" & MY_START & ":F" & MY_END & ")"
        Range("F" & r).Copy Range("G" & r & ":I" & r)
        r = r + 1
    End If
Next r
End Sub

 

[luke6843]

Wow, works perfectly. I understand how you added the second row and how you got it to add only the necessary rows. This is great! Thanks!

Luke