Create a macro

Solved/Closed
Report
Posts
8
Registration date
Tuesday August 6, 2013
Status
Member
Last seen
August 21, 2013
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

Please help me.

I am new at macros, and I need to create a macro that will automatically total a column as below:

# Name Amount Total
0234 Brown, j 15.00
0234 Brown, J 30.00 45.00
0123 Junior, C 34.00
0123 Junior, C 30.00 64.00

Both the number in column 1 and the name in column 2 must match, I want it to sum down until these two are no longer true, then put the total in column D. Any help on this would be greatly appreciated,

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
why macro?
see
http://speedy.sh/c3PRs/louannc.docx

if macro is reqd click record macro
follow lsteps
and stop recording
see the recorded macro and edit it if necessary
Posts
8
Registration date
Tuesday August 6, 2013
Status
Member
Last seen
August 21, 2013

Thank you for your input; however, a pivot table will not work for my needs. I simplified the spreadsheet, but there are over 25 columns on the spreadsheet, and I have to run a monthly report with all this info, and I need it to total the amounts for each #, Name in a separate column as I described on the spreadsheet. I was hoping that a I could run a macro each month that would auto total these for me. Is it possible? I would be grateful for any help anyone can give.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
data is like this
number Name Amount Total
234 Brown, j 15
234 Brown, J 30
123 Junior, C 34
123 Junior, C 30


try this macro

Sub test()

    Dim r1 As Range, c1 As Range, r As Range, filt As Range, filt1 As Range
    Dim cfilt As Range, ssum As Double, j As Integer
    Worksheets("sheet1").Activate
    Range("E1").EntireColumn.Delete

    Set r1 = Range(Range("A1"), Range("A1").End(xlDown))
    Set r = Range("A1").CurrentRegion
    r.Sort Key1:=Range("B1"), Header:=xlYes
    Set filt = Range("A1").End(xlDown).Offset(5, 0)
    r1.AdvancedFilter xlFilterCopy, , filt, True
    Set filt = Range(filt.Offset(1, 0), filt.End(xlDown))

    For Each cfilt In filt
        r.AutoFilter field:=1, Criteria1:=cfilt
        ssum = WorksheetFunction.Sum(r.Columns("D:D").SpecialCells(xlCellTypeVisible))
        j = Range("d1").End(xlDown).Row
        Cells(j, "E") = ssum
        ssum = 0



    Next cfilt
    ActiveSheet.AutoFilterMode = False
    Range(Range("A1").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A")).EntireRow.Delete

End Sub