Create a macro

Solved/Closed
louannc Posts 7 Registration date Tuesday August 6, 2013 Status Member Last seen August 21, 2013 - Aug 6, 2013 at 05:01 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Aug 8, 2013 at 02:47 AM
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 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Aug 7, 2013 at 04:26 AM
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
louannc Posts 7 Registration date Tuesday August 6, 2013 Status Member Last seen August 21, 2013
Aug 7, 2013 at 09:18 AM
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.
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Aug 8, 2013 at 02:47 AM
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