Add Totals Column on multiple sheets

jayped

Board Regular
Joined
Mar 20, 2019
Messages
54
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'
NameID No.Total
John1001250.00
Luke1002460.00

<tbody>
</tbody>
There are many columns between ID No. and Total

'Travel Advance'
NameID No.Total
John1001500.00
Luke1002700.00


<tbody>
</tbody>


The Summary Sheet will look like this:
NameID No.B/FJulAugSepOctNovDecJanFebMarAprMayJunTotalBalance as Per ScheduleVariance
John1001750.00
Luke10021160.00

<tbody>
</tbody>



Thank you!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
 
Upvote 0
Good day,

I have a workbook containing 6 sheets and a "summary" sheet.

In each sheet (including Summary) "Name" and "ID No." are contained in columns A and B respectively, headers are in row 5 and the first name and ID no begins row 8 for all sheets.

The six sheets are as follows:


'Credit Card'
A B
NameID No.Total
John1001250.00
Luke1002460.00

<tbody>
</tbody>

 
Upvote 0
Good day,

I have a workbook containing 5 sheets and a "summary" sheet.

In each sheet (including Summary) "Name" and "ID No." are contained in columns A and B respectively, headers are in row 5 and the first name and ID no begins row 8 for all sheets.

The six sheets are as follows:


'Credit Card' - "Total" is contained in Column DK

ABDK
NameID No.Total
John1001250.00
Luke1002460.00

<tbody>
</tbody>


'Travel' - "Total" is contained in Column DG

ABDG
NameID No.Total
John1001350.00
Luke1002860.00

<tbody>
</tbody>

'Petty Cash' - "Total" is contained in Column BK

ABBK
NameID No.Total
John100150.00
Luke100260.00

<tbody>
</tbody>

'Loan' - "Total" is contained in Column BL

ABBL
NameID No.Total
John100150.00
Luke100260.00

<tbody>
</tbody>

'Airfare' - "Total" is contained in Column BK

ABBK
NameID No.Total
John1001700.00
Luke10020.00

<tbody>
</tbody>


Summary Sheet:

ABCDEFGHIJKLMNOPQR
NameID no.B/FJulAugSepOctNovDecJanFebMarAprMayJunFY20Balance as Per ScheduleVariance
John10011300.00
Luke10021440.00

<tbody>
</tbody>


I would like the Total columns in each of the 5 sheets above be summed and placed in column Q on the summary sheet.
Is this possible?

I hope this makes some sense.


Thank you.
 
Upvote 0
Good day,

I have a workbook containing 5 sheets and a "summary" sheet.

In each sheet (including Summary) "Name" and "ID No." are contained in columns A and B respectively, headers are in row 5 and the first name and ID no begins row
8 for all sheets.

Considerations:
- The column is located in row 5 of each sheet where the word "Total" is found.
- The data begins in row 8.

A detail you did not mention, if the ID of a sheet does not exist in the Summary sheet, what to do?

Code:
Sub Totals_multiple_sheets()
  Dim sh As Worksheet, su As Worksheet, i As Long, f As Range, lr As Long, col As Long
  Set su = Sheets("Summary")
  For Each sh In Sheets
    If sh.Name <> su.Name Then
      For i = [COLOR=#ff0000]8[/COLOR] To sh.Range("B" & Rows.Count).End(xlUp).Row
        Set f = sh.Rows([COLOR=#ff0000]5[/COLOR]).Find("[COLOR=#ff0000]Total[/COLOR]", , xlValues, xlWhole)
        col = f.Column
        If Not f Is Nothing Then
          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.Cells(i, col).Value          
          End If
        End If
      Next
    End If
  Next
End Sub
 
Upvote 0
Thank you for your help.

In response to your question: if the ID of a sheet does not exist in the Summary sheet, what to do? - Normally this should not happen and i can't think of what can be done in this situation.

Also, I am getting the Run-time error '91': Object variable or With block variable not set and its occuring on the line 'col = f.column'
 
Upvote 0
Also, I am getting the Run-time error '91': Object variable or With block variable not set and its occuring on the line 'col = f.column'

That error is because in some of the sheets there is no word "Total" in row 5.
Check that you actually have the word "Total" in row 5 on the sheets and that it has no spaces left or right or any other text, as I put it in the considerations, the word should be "Total".

Try this:
Code:
Sub Totals_multiple_sheets()
  Dim sh As Worksheet, su As Worksheet, i As Long, f As Range, lr As Long, col As Long
  Set su = Sheets("Summary")
  For Each sh In Sheets
    If sh.Name <> su.Name Then
      For i = 8 To sh.Range("B" & Rows.Count).End(xlUp).Row
        Set f = sh.Rows(5).Find("Total", , xlValues, xlWhole)
        If Not f Is Nothing Then
          col = f.Column
          If Not f Is Nothing Then
            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.Cells(i, col).Value
            End If
          End If
        End If
      Next
    End If
  Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top