Add Totals Column on multiple sheets

[jayped]

Good day,

I have a workbook containing multiple sheets and a 'Summary' sheet. I have a column in the Summary sheet named 'Balance as Per Schedule' and I want to sum the amounts in the 'Total' columns on each sheet by using the ID No as the criteria.

Basically there is a column for 'Total' on each sheet and ID No on each of those sheets as well. I want the total of those Totals on the Summary Sheet for every ID number.

The ID Numbers are in column B from row 4 in each sheet. The "Balance as Per Schedule' column is column Q beginning row 4 as well.

The other sheets are named 'Credit Card', 'Travel Advance', etc, for example:

'Credit Card'
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]250.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]460.00[/TD]
[/TR]
</tbody>[/TABLE]
There are many columns between ID No. and Total

'Travel Advance'
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]500.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]700.00

[/TD]
[/TR]
</tbody>[/TABLE]


The Summary Sheet will look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]B/F[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Total[/TD]
[TD]Balance as Per Schedule[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]750.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1160.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thank you!

 

[DanteAmor]

Hi@jayped,

Basically there is a column for 'Total' on each sheet

You didn't mention which column, in the macro I put column "C", change it if necessary.

Try this

Sub Totals_multiple_sheets()
  Dim sh As Worksheet, su As Worksheet, i As Long, f As Range, lr As Long
  Set su = Sheets("[COLOR=#0000ff]Summary[/COLOR]")
  For Each sh In Sheets
    If sh.Name <> su.Name Then
      For i = 4 To sh.Range("B" & Rows.Count).End(xlUp).Row
        Set f = su.Range("B:B").Find(sh.Range("B" & i).Value, , xlValues, xlWhole)
        If Not f Is Nothing Then
          su.Range("Q" & f.Row).Value = su.Range("Q" & f.Row).Value + sh.Range("[COLOR=#ff0000]C[/COLOR]" & i).Value
        Else
          lr = su.Range("B" & Rows.Count).End(xlUp).Row + 1
          su.Range("A" & lr).Value = sh.Range("A" & i).Value
          su.Range("B" & lr).Value = sh.Range("B" & i).Value
          su.Range("Q" & lr).Value = sh.Range("[COLOR=#ff0000]C[/COLOR]" & i).Value
        End If
      Next
    End If
  Next
End Sub

 

[jayped]

Thank you for your reply.

What if the Total column in each sheet is not in the same column?

 

[DanteAmor]

There should be some reference to search for the column.

 

[jayped]

What should I change the "C" to if the column letter for totals will vary across the workbook?

 

[DanteAmor]

What should I change the "C" to if the column letter for totals will vary across the workbook?

I am not understanding what you need.

You have this:

'Credit Card'
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]250.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]460.00[/TD]
[/TR]
</tbody>[/TABLE]


In which column you have the column "Total"

You never mentioned in which column you have Total, I assumed it was column "C". But if it is not the "C" and it is the "M", then change in the macro the letter "C" for an "M".
I don't know what the column is, because I'm not seeing your sheets.

After updating the column in the macro, you try the macro and tell me.
If the macro works for the original requirement you put comments. If you later want to make a change, I will gladly review it.

 

[jayped]

Ok. In the sheet below 'Total' is column DG

'Credit Card'
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]250.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]460.00[/TD]
[/TR]
</tbody>[/TABLE]

There are many columns between ID No. and Total


And 'Total' below is column DK
'Travel Advance'
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]500.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]700.00[/TD]
[/TR]
</tbody>[/TABLE]


And there is another sheet and 'Total' is in column BL.

How would I get around this?

 

[DanteAmor]

Ok. In the sheet below 'Total' is column DG

And 'Total' below is column DK

And there is another sheet and 'Total' is in column BL.

How would I get around this?

As I mentioned in post #4 , there should be a reference or a pattern in all the sheets to know in which column the total is. In fact, it could be the word "Total", if in the header row there is only one "Total" word on each sheet, that is our reference.
I hope that the header row is only in a specific row. Well, you must also tell me which line the header is on.

 

[jayped]

The header is on line 5.

 

[DanteAmor]

So what is the reference?