asked on

Add up Totals from a list

Hello,

I have a spreadsheet as attached.

On Sheet 1 I have names listed from B13 downwards - these names change day to day - there may be 5 or 40 however each name on this list always remains unique.

On Sheet 2 I have the names (the same names as listed on Sheet 1 but on many rows) in column F. On the same row as each name in column J I have a number.

I would like to, via VBA, go down the list of names as shown on sheet1 from B13 down (until there are no more names) and against each name in C13, 14 etc.. put a total.

The total would be a sum of column J (from sheet2) where that name appears in column (from sheet2) and shown in a total on Sheet 1 in B13,14 etc for each name.

Sorry If this is not clear, please let me know if I need to help explain it any further ;)

Thanks,

GISVPN
Example-1.xlsx

Saqib Husain

Try

Sub subtotals()
Range("C13", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=sumif(sheet2!c6,rc[-1],sheet2!c10)"
Range("C13", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).Value = Range("C13", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).Value
End Sub

Open in new window

gisvpn

ASKER

That does not seem to work.

I get a prompt to save the workbook each time I run the macro.

Can I specify in the code this is to look only at C13 downwards for any names in B13 downwards only on the Summary tab of the workbook.
I like the idea of a formula being used; )

Saqib Husain

You ought to be running the code from the summary sheet and also make sure that the name of the data sheet is sheet2. If the data sheet has some other name then change all instances of sheet2 in the formula to whatever name you have.

Have you tried the macro on the file you uploaded?

Steve

Attached is a file which puts the sumif formula in the cell C13 onward.
this is after creating list with hyperlinks to sheets.

See attached:

Sub DataNamesMake()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Summary")
Set ws2 = wb.Sheets("data")
x = ws.Cells(Rows.Count, 2).End(xlUp).Row + 1
y = 1

Do While Len(ws2.Cells(y, 6)) <> 0
If WorksheetFunction.CountIf(ws.Range("B:B"), ws2.Cells(y, 6).Value) = 0 Then
    ws.Hyperlinks.Add Anchor:=ws.Cells(x, 2), Address:="", SubAddress:=ws2.Cells(y, 6).Value & "!A1", TextToDisplay:=ws2.Cells(y, 6).Value
    ws.Cells(x, 3).FormulaR1C1 = "=SUMIF(Data!C[3],RC[-1],Data!C[7])"
    x = x + 1
End If
y = y + 1
Loop
End Sub

Open in new window

Example-1.xlsm

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

Or you could set up a pivot table.