Create a macro Solved/Closed

Question

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,

Configuration: Windows 7 / Internet Explorer 10.0

venkat1926 · Answer

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 · Answer

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 · Answer

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

Create a macro

3 responses

Similar discussions

Subscribe To Our Newsletter!