Create a macro

Solved/Closed
louannc
Posts
8
Registration date
Tuesday August 6, 2013
Status
Member
Last seen
August 21, 2013
- Aug 6, 2013 at 05:01 PM
venkat1926
Posts
1864
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 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
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
0
louannc
Posts
8
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.
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
810
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


0