Sum final column after creating worksheet

[cdrobinson83]

Hello,

I have the below macro, graciously provided to me in these forums. I'm looking to try to add a step where after creating the individual worksheets, it then sums the FINAL column of the new worksheet. I say "final' column because depending on the data, the column needing to be summed will not always be in the same location. However, it will always be the last column in the data. Is anyone please able to assist?

Sub CREATE()
    Dim a, i&, ii&, s$, r As Range, c As Range, dic As Object
    Application.ScreenUpdating = False
    Set dic = CreateObject("Scripting.Dictionary")
    Set r = Sheets("sheet1").[a1].CurrentRegion
    Set c = r.Offset(, r.Columns.Count + 2).Range("a1:a2")
    a = Application.Index(r, Application.Sequence(r.Rows.Count, , 1, 1), [{8,4,12}])
    For i = 2 To UBound(a, 1)
        s = Join(Array(a(i, 1), a(i, 2), a(i, 3)), "_")
        If Not dic.exists(s) Then
            dic(s) = Empty
            If Not Evaluate("isref('" & s & "'!a1)") Then
                Sheets.Add(, Sheets(Sheets.Count)).Name = s
            End If
            For ii = 1 To UBound(a, 2)
                If TypeName(a(i, ii)) = "String" Then a(i, ii) = Chr(34) & a(i, ii) & Chr(34)
            Next
            With Sheets(s)
                .UsedRange.CLEAR
                r.Rows(1).Copy .[a1]
                For ii = 1 To r.Columns.Count
                    .Columns(ii).ColumnWidth = r.Columns(ii).ColumnWidth
                Next
                c(2).Formula = "=and(h2=" & a(i, 1) & ",d2=" & a(i, 2) & ",l2=" & a(i, 3) & ")"
                r.AdvancedFilter 2, c, .[a1].CurrentRegion
            End With
        End If
    Next
    c.CLEAR
    Application.ScreenUpdating = True
End Sub

 

[Fuji]

Insert bold lines

                r.AdvancedFilter 2, c, .[a1].CurrentRegion
                With .Range("a" & Rows.Count).End(xlUp)(2, r.Columns.Count - 1).Resize(, 2)
                    .FormulaR1C1 = Array("Total", "=sum(r2c:r[-1]c)")
                    .Font.Bold = True
                    .Borders.Weight = 2
                    .Borders.ColorIndex = 15
                End With
            End With

 

[Peter_SSs]

@cdrobinson83
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[cdrobinson83]

Insert bold lines

                r.AdvancedFilter 2, c, .[a1].CurrentRegion
                With .Range("a" & Rows.Count).End(xlUp)(2, r.Columns.Count - 1).Resize(, 2)
                    .FormulaR1C1 = Array("Total", "=sum(r2c:r[-1]c)")
                    .Font.Bold = True
                    .Borders.Weight = 2
                    .Borders.ColorIndex = 15
                End With
            End With

Thank you AGAIN!! This worked perfectly.

 

[cdrobinson83]

@cdrobinson83
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Thank you for this. I will be sure to use the proper tags going forward. I truly appreciate you letting me know.