Summing based on data range

[Tinku]

I know this wil be an easy thing for you Excel gurus. I am having a hard time trying to build this macro.
What I am trying to achieve is to to create a macro for summing from data which I get every week. The number of rows keep changing. I tried recording the macro but its not flexible and does not take into consideration any changes in the rows in the new data.
I have u/l a sample template sheet.
Please help me with this macro.

Regards
Tinku

[mdmackillop]

Hi Tinku,
As a start, this should put the formulae into cell I96:M96, based on the relative position of Section 6 and Section 7 in column B. No more time just now.

Sub Macro2()
With Range("B:B")
MyStart = .Find(What:="Section 6:", After:=[B1], LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
MyEnd = .Find(What:="Section 7:", After:=[B1], LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
End With
Cells(MyStart + 1, "I").Formula = "=SUMIF($AA" & MyStart + 9 & ":$AA" & MyEnd - 2 _
& "," & Chr(34) & "SMB" & Chr(34) & ", I" & MyStart + 9 & ":I" & MyEnd - 2 & ")"
Cells(MyStart + 1, "I").Range("A1:E1").FillRight
End Sub

[Tinku]

Thank you mdmackillop

The code is working but it is adding some extra rows in the formulae. I just copied some rows and pasted them below existing rows and ran the macro. I noticed that the sumif() formulae take some extra rows into consideration in Section 6 and section 8.
Please note the 2 sections are sections 6 and section 8. I changed the section # in the code ..could that be the reason that the macro is not working properly ?

Thank you

Regards
Tinku

[mdmackillop]

Hi Tinkyu,
I was in a bit of a rush earlier.
The code works by finding the text "Section 6:" and "Section 7:" Section 6 location gives a row number for MyStart and Section 7 for MyEnd. The formula code uses offsets from these values for the row numbers in the formula, so MyEnd - 2 would become MyEnd - 4 to give you row 167. If the space between Sections 6 and 7 is not consistent, then you could use one of the following alternatives, depending upon whether your Column B data is guaranteed to have no gaps or not. With either of these MyEnd - 2 would simply be changed to MyEnd

MyEnd = .Find(What:="Section 6:", After:=[B1], LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Offset(9, 0).End(xlDown).Row
MyEnd = .Find(What:="Section 7:", After:=[B1], LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).End(xlUp).Row

[mdmackillop]

"Finished product" (I hope!)

Option Explicit

Sub Macro2()
Dim MyStart As Long, MyEnd As Long, i As Long
    Dim MySections, MS
MySections = Array(6, 8)
For Each MS In MySections
        With Range("B:B")
            MyStart = .Find(What:="Section " & MS & ":", After:=[B1], LookIn:=xlFormulas _
            , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Row
MyEnd = .Find(What:="Section " & MS & ":", After:=[B1], LookIn:=xlFormulas _
            , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(9, 0).End(xlDown).Row
End With
Cells(MyStart + 1, "I").Formula = "=SUMIF($AA" & MyStart + 9 & ":$AA" & MyEnd _
        & "," & Chr(34) & "SMB" & Chr(34) & ", I" & MyStart + 9 & ":I" & MyEnd & ")"
        Cells(MyStart + 1, "I").Range("A1:E1").FillRight
Cells(MyStart + 4, "I").Formula = "=SUM(I" & MyStart + 5 & ":I" & MyEnd & ")"
        Cells(MyStart + 4, "I").Range("A1:E1").FillRight
    Next
End Sub

[Tinku]

Awesome mdmackillop

Its working wonderfully.. can I run(loop) this macro on 6 worksheets with different names (eg Japan, Americas, Pacific, Africa, SE Asia, Europe)

Thanks so much.. its going to save me significant amount of time. Now I can take more coffee breaks

Regards
Tinku

[mdmackillop]

Hi Tinku,
I've added another loop. Replace Avent1, 2 & 3 with sheet names as required.
Now you should have time for a cream bun as well!

Option Explicit

Sub Macro2()
Dim MyStart As Long, MyEnd As Long, i As Long
    Dim MySections, MS, MySheets, Sh
    Dim ThsSht As String
ThsSht = ActiveSheet.Name
    Application.ScreenUpdating = FalseMySheets = Array("Avent", "Avent1", "Avent2")
    For Each Sh In MySheets
        Sheets(Sh).Activate
            MySections = Array(6, 8)
For Each MS In MySections
                With Range("B:B")
                    MyStart = .Find(What:="Section " & MS & ":", After:=[B1], LookIn:=xlFormulas _
                    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False).Row
MyEnd = .Find(What:="Section " & MS & ":", After:=[B1], LookIn:=xlFormulas _
                    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False).Offset(9, 0).End(xlDown).Row
End With
Cells(MyStart + 1, "I").Formula = "=SUMIF($AA" & MyStart + 9 & ":$AA" & MyEnd _
                & "," & Chr(34) & "SMB" & Chr(34) & ", I" & MyStart + 9 & ":I" & MyEnd & ")"
                Cells(MyStart + 1, "I").Range("A1:E1").FillRight
Cells(MyStart + 4, "I").Formula = "=SUM(I" & MyStart + 5 & ":I" & MyEnd & ")"
                Cells(MyStart + 4, "I").Range("A1:E1").FillRight
            Next
Next
    Sheets(ThsSht).Activate
    Application.ScreenUpdating = True
End Sub

[Tinku]

Now you should have time for a cream bun as well!

Thanks so much mdmackillop

I think I can sneak out for a few hours every week now.. while the data is being
"PROCESSED"..

Regards
Tinku