Need VBA code to total the cells above rolling the total to the next summation

[tmarinho]

Very new to VBA and trying to learn as much as i can on the fly. Need some serious help with this!

I have a file that pulls all the changes made during a certain period and sorts them based on date and what it is assigned too (budget, outlook03, outlook05, etc.) I would like to be able to automatically sum up the changes to get me a total for each assigned outlook. For example: I start at 100 there are 3 different changes (3 different rows) for Outlook02 the total would be 100+the 3 rows. Then i want to use that last total and add the next few changes to get the total for outlook03 and so on.

Below i have an example of my sheet. I need to automatically sum the starting budget number of 31 and all the outlook03 changes to get a total Outlook03. I then need to use that new total and add the changes of outlook07 to get the total of Outlook07 and so on. I need to do this for columns G to AX.

the number of changes in any given outlook will vary. I couldn't think of vba code that would work for this. Please help if this is at all possible.

	a	b	c	d	e	f	g	h	i
	Budget							31.0	35.0
	Date	Category	Change Type	Detailed Comments	Assign Change to	Step		Jan-17	Feb-17
	3/7/2017	Other/Timing	AHT	work	Outlook03			1.0	1.0
	3/15/2017	Lost Efficiencies	Driver	ok it has too	Outlook03			1.0	1.0
	3/29/2017	Efficiency Initiatives	Frequency	blank	Outlook03			1.0	1.0
	4/5/2017	Volume	AWOP	blank2	Outlook03			1.0	1.0
	4/26/2017	Efficiency Initiatives	AHT	last one	Outlook03			1.0	1.0
					TOTAL OUTLOOK03
	4/26/2017	Invest	AHT	gftrgsf	Outlook07			3.0	4.0

[mdmackillop]

Can you post a sample workbook showing data and expected outcome. Go Advanced/Manage Attachments

[tmarinho]

I attached an example workbook. The first tab is what I'm working with, the second is what i need it to look like.

[mdmackillop]

This assumes that the Budget Row exists in the target worksheet (Sheet2)

Option Explicit


Sub Test()
    Dim wsS As Worksheet
    Dim wsT As Worksheet
    Dim Tgt As Range
    Dim Rng As Range, R1 As Range, R2 As Range
    Dim Tot As Range
    Dim Rw As Long, Cel As Range
    
    Set wsS = Sheets("Sheet1")
    Set wsT = Sheets("Sheet2")
    
    With wsSs
        Set R1 = .Columns(1).Find("Start").Offset(1)
        Set R2 = .Cells(Rows.Count, 1).End(xlUp)
    End With
    
    Set Rng = Range(R1, R2).Resize(, 20)
    
    Set Tgt = wsT.Cells(Rows.Count, 1).End(xlUp)(2)
    Rng.Copy Tgt
    Rw = Tgt(0).Row
    Set R1 = Tgt.Offset(1)
    Set R2 = wsT.Cells(Rows.Count, 1).End(xlUp)(2)
    Set Rng = Range(R1, R2).Resize(, 20)
    Set Tot = Rng.Columns(5).SpecialCells(xlCellTypeBlanks)
    
    For Each Cel In Tot.Cells
        Cel.Value = Cel(0) & " Total"
        Cel.Offset(, 3).Resize(, 13).FormulaR1C1 = "=SUBTOTAL(9," & "R" & Rw & "C:RC)"
        With Cel.Offset(, -4).Resize(, 20)
            .Font.Bold = True
            .Interior.Color = 13434879
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
        End With
    Next Cel
End Sub