VBA Code to add in blank rows at a specific point, then do a formula in that blank row

[lichldo]

Hello - I'm looking for some help to create a VBA code macro. I have a report that I can export from our project/finance system that I need to manipulate a bit. This is a sample of what the raw data looks like (have of course modified some of the data for privacy) -

Dataviewer (75).csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Company	Opp Status	Client/Prospect	Client	Job	Job description	Opp Weighting	Client Lead	Job Lead	Phase Lead	Account Director	Project Director	23-Jul	23-Aug	23-Sep	23-Oct	23-Nov	23-Dec	Total
2	UK	Status	Client	Client Name	1/00112120	Job Decription	75%	xxx	Name 1	xxxx	xxxx	xxxx	8000	15000	10000	8000	3000	2500	46500
3	UK	Status	Client	Client Name	1/00111891	Job Decription	100%	xxx	Name 1	xxxx	xxxx	xxxx	4000	4000	4000	0	0	0	12000
4	UK	Status	Client	Client Name	1/00111645	Job Decription	100%	xxx	Name 2	xxxx	xxxx	xxxx	5000	0	0	0	0	0	5000
5	UK	Status	Client	Client Name	1/00111951	Job Decription	100%	xxx	Name 2	xxxx	xxxx	xxxx	5000	0	0	0	0	0	5000
6	UK	Status	Client	Client Name	1/00112020	Job Decription	100%	xxx	Name 2	xxxx	xxxx	xxxx	3000	0	0	0	0	0	3000
7	UK	Status	Client	Client Name	1/00112240	Job Decription	25%	xxx	Name 3	xxxx	xxxx	xxxx	0	0	35000	35000	25000	0	95000
8	UK	Status	Client	Client Name	1/00112099	Job Decription	50%	xxx	Name 3	xxxx	xxxx	xxxx	0	8000	15000	50000	50000	0	123000
9	UK	Status	Client	Client Name	1/00112121	Job Decription	75%	xxx	Name 3	xxxx	xxxx	xxxx	1000	12000	5000	0	0	0	18000
10	UK	Status	Client	Client Name	1/00112122	Job Decription	75%	xxx	Name 3	xxxx	xxxx	xxxx	0	1000	12000	5000	0	0	18000
11	UK	Status	Client	Client Name	1/00111830	Job Decription	100%	xxx	Name 3	xxxx	xxxx	xxxx	14744	0	0	0	0	0	14744
12	UK	Status	Client	Client Name	1/00111912	Job Decription	100%	xxx	Name 3	xxxx	xxxx	xxxx	15000	35000	35000	20000	12500	7000	124500
13	UK	Status	Client	Client Name	1/00112231	Job Decription	100%	xxx	Name 3	xxxx	xxxx	xxxx	-5000	-5600	-5200	-6880	0	0	-22680
14	UK	Status	Client	Client Name	1/00112244	Job Decription	0%	xxx	Name 4	xxxx	xxxx	xxxx	36418	7163.14	0	0	0	0	43581.14
15	UK	Status	Client	Client Name	1/00112108	Job Decription	25%	xxx	Name 4	xxxx	xxxx	xxxx	22291.17	7327.7	0	0	0	0	29618.87
16	UK	Status	Client	Client Name	1/00112172	Job Decription	100%	xxx	Name 4	xxxx	xxxx	xxxx	14796.76	5483.51	0	201061.89	201061.89	207154.67	629558.72
17	UK	Status	Client	Client Name	1/00112052	Job Decription	100%	xxx	Name 4	xxxx	xxxx	xxxx	26406.13	26406.13	26406.13	26406.13	26406.13	26406.13	158436.78
18	UK	Status	Client	Client Name	1/00112054	Job Decription	100%	xxx	Name 4	xxxx	xxxx	xxxx	26406.13	26406.13	26406.13	0	0	0	79218.39
Raw Data

I have manually done what I would like the macro to be able to do for me. I need it to do the following actions -
1. Add a blank row in-between each "new section" of column I - so everytime there is a new name in column I, add in a new blank row
2. Within that, add in a blank row between anything less than 100% in G. So we will have a range of percentages in G, anything less than 100% we consider an "opp" so we want all of those together
3. Add in another blank row after all the 100% in G - since that will be at the end of that "Section", there will be 2 blank rows (including the one added from #1 in this list)
4. Then I need to total each section in the blank row for columns M thru S - so that we have totals for all the opps and totals for all the 100% jobs
5. Then I need to total everything together (without double counting the totals we've added) to see the total amount each I section has

Here is what that looks like when I did it manually -

Dataviewer (75).csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Company	Opp Status	Client/Prospect	Client	Job	Job description	Opp Weighting	Client Lead	Job Lead	Phase Lead	Account Director	Project Director	23-Jul	23-Aug	23-Sep	23-Oct	23-Nov	23-Dec	Total
2
3	UK	Status	Client	Client Name	1/00112120	Job Description	75%	xxx	Name 1	xxx	xxx	xxx	8000	15000	10000	8000	3000	2500	46500
4												Opp Total	8000	15000	10000	8000	3000	2500	46500
5	UK	Status	Client	Client Name	1/00111891	Job Description	100%	xxx	Name 1	xxx	xxx	xxx	4000	4000	4000	0	0	0	12000
6												Live Job Total	4000	4000	4000	0	0	0	12000
7												Job Lead Total	12000	19000	14000	8000	3000	2500	58500
8	UK	Status	Client	Client Name	1/00111645	Job Description	100%	xxx	Name 2	xxx	xxx	xxx	5000	0	0	0	0	0	5000
9	UK	Status	Client	Client Name	1/00111951	Job Description	100%	xxx	Name 2	xxx	xxx	xxx	5000	0	0	0	0	0	5000
10	UK	Status	Client	Client Name	1/00112020	Job Description	100%	xxx	Name 2	xxx	xxx	xxx	3000	0	0	0	0	0	3000
11												Live Job Total	13000	0	0	0	0	0	13000
12												Job Lead Total	13000	0	0	0	0	0	13000
13	UK	Status	Client	Client Name	1/00112240	Job Description	25%	xxx	Name 3	xxx	xxx	xxx	0	0	35000	35000	25000	0	95000
14	UK	Status	Client	Client Name	1/00112099	Job Description	50%	xxx	Name 3	xxx	xxx	xxx	0	8000	15000	50000	50000	0	123000
15	UK	Status	Client	Client Name	1/00112121	Job Description	75%	xxx	Name 3	xxx	xxx	xxx	1000	12000	5000	0	0	0	18000
16	UK	Status	Client	Client Name	1/00112122	Job Description	75%	xxx	Name 3	xxx	xxx	xxx	0	1000	12000	5000	0	0	18000
17												Opp Total	1000	21000	67000	90000	75000	0	254000
18	UK	Status	Client	Client Name	1/00111830	Job Description	100%	xxx	Name 3	xxx	xxx	xxx	14744	0	0	0	0	0	14744
19	UK	Status	Client	Client Name	1/00111912	Job Description	100%	xxx	Name 3	xxx	xxx	xxx	15000	35000	35000	20000	12500	7000	124500
20	UK	Status	Client	Client Name	1/00112231	Job Description	100%	xxx	Name 3	xxx	xxx	xxx	-5000	-5600	-5200	-6880	0	0	-22680
21												Live Job Total	24744	29400	29800	13120	12500	7000	116564
22												Job Lead Total	25744	50400	96800	103120	87500	7000	370564
23	UK	Status	Client	Client Name	1/00112244	Job Description	0%	xxx	Name 4	xxx	xxx	xxx	36418	7163.14	0	0	0	0	43581.14
24	UK	Status	Client	Client Name	1/00112108	Job Description	25%	xxx	Name 4	xxx	xxx	xxx	22291.17	7327.7	0	0	0	0	29618.87
25												Opp Total	58709.17	14490.84	0	0	0	0	73200.01
26	UK	Status	Client	Client Name	1/00112172	Job Description	100%	xxx	Name 4	xxx	xxx	xxx	14796.76	5483.51	0	201061.89	201061.89	207154.67	629558.72
27	UK	Status	Client	Client Name	1/00112052	Job Description	100%	xxx	Name 4	xxx	xxx	xxx	26406.13	26406.13	26406.13	26406.13	26406.13	26406.13	158436.78
28	UK	Status	Client	Client Name	1/00112054	Job Description	100%	xxx	Name 4	xxx	xxx	xxx	26406.13	26406.13	26406.13	0	0	0	79218.39
29												Live Job Total	67609.02	58295.77	52812.26	227468.02	227468.02	233560.8	867213.89
30												Job Lead Total	126318.19	72786.61	52812.26	227468.02	227468.02	233560.8	940413.9
What I Want

Cell Formulas
Range	Formula
M4:S4,M6:S6	M4	=SUM(M3)
M7:S7	M7	=SUM(M3,M5)
M11:S11,M29:S29,M21:S21	M11	=SUM(M8:M10)
M12:S12	M12	=SUM(M8:M10)
M17:S17	M17	=SUM(M13:M16)
M22:S22	M22	=SUM(M13:M16,M18:M20)
M25:S25	M25	=SUM(M23:M24)
M30:S30	M30	=SUM(M23:M24,M26:M28)

 

[Peter_SSs]

Try this with a copy of your data.

Sub Insert_Totals()
  Dim i As Long
  Dim rA As Range, rFound As Range
  
  Application.ScreenUpdating = False
  For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Range("I" & i).Value <> Range("I" & i - 1).Value Then Rows(i).Insert
  Next i
  For Each rA In Range("G2", Range("G" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
    With rA
      Set rFound = .Find(What:="100%", After:=.Cells(.Count), LookAt:=xlWhole, Searchdirection:=xlNext)
      If Not rFound Is Nothing Then
        Rows(.Row + .Count).Insert
        With .Offset(.Count, 5).Resize(2, 8)
          .Rows(1).FormulaR1C1 = Replace(Replace("=sumifs(R#C:R[-1]C,R#C@:R[-1]C@,1)", "#", rA.Row), "@", rA.Column)
          .Rows(2).FormulaR1C1 = Replace(Replace("=sumifs(R#C:R[-2]C,R#C@:R[-2]C@,""<>"")", "#", rA.Row), "@", rA.Column)
          .Columns(1).Value = Application.Transpose(Array("Live Job Total", "Job Lead Total"))
          .Font.Bold = True
        End With
        If rFound.Row <> .Row Then
          rFound.EntireRow.Insert
          With Range("L" & rFound.Row - 1).Resize(, 8)
            .FormulaR1C1 = "=sum(R" & rA.Row & "C:R[-1]C)"
            .Cells(1).Value = "Opp Total"
            .Font.Bold = True
          End With
        End If
      End If
    End With
  Next rA
  Application.ScreenUpdating = True
End Sub