Summary Sheet of subtotals from mult. sheets

Microsoft Excel 2003 (full product)
January 15, 2010 at 11:14:37
Specs: Windows 2003
Need some help...
I have multiple worksheets (labeled 1-60) with data that has been subtotaled using the subtotal feature in Excel.
Example of subtotal set up

Sheet 1
Type: $$Cost $$Depreciation $$ect...
mme: $2000.00 $40 $433.00
ff: $1500.00 $35 $510.00
TOTAL: $3500 $75 $943.00

Sheet 2
Type: $$Cost $$Depreciation $$ect...
mme: $3000.00 $50 $200.00
ff: $1500.00 $45 $510.00
TOTAL: $4500 $95 $710.00

There are a total of 6 columns being subtotaled by type, I need the summary to open each spreadsheet and just give me all the TOTALs... Pivot table won't work because of the subtotal function. I also can't write a formula to grab Cells L:Q becuase the TOTAL is in a different row for each sheet!! Is there a Macro for this? Help!

Summary sheet would pull only the TOTALS:

Sheet $$Cost $$Depreciation $$ect...
Sheet1 3500 75 943.00
Sheet2 4500 95 710.00

See More: Summary Sheet of subtotals from mult. sheets

Report •

January 15, 2010 at 12:24:31

I think that I have answered this in the previous post.

You don't need macros for this, because any changes in values on the various worksheets won't be automatically reflected on the summary page. So formulas are better.

Just put links to the totals on each worksheet in the summary cells.

If you use the same cells for the totals on each worksheet, creating a series of summary formulas should be easy.

Create ='1101'!B$501
Drag to the right to make '1101'!C$501 and '1101'!D$501
Drag all three down a row, select all three and do a find - replace to change 1101 to 1102. etc...

Another way to make a lot of similar formulas is to make them as text, no = signs, and prefix them with XXYY
Use Excel's automatic series to create sequential numbers, and create the formulas by concatenating words and numbers.
Then Copy and Paste Special as Values, and finally do a Find-Replace to replace XXYY with =
and you have your formulas

I note that you refer to the SUBTOTAL() function
Is there a special reason for using this rather than SUM(), as you appear to be summing a few $ values. Are you filtering the data - and need the sum of visible cells only?


Report •

January 15, 2010 at 12:39:10
I have a variety of account types in each spreadsheet and need them subtotaled by spreadsheet. The summary sheet, however, doesn't need the subtotals just the Grand totals.
Acct Type: $$ $$$ $$$$
MME $10 $100
MME $15 $120
MME $25 $150
FF $100 $1000
FF $100 $5000
You get the idea....

So the spreadsheets need to be by subtotal but the summary spreadsheet needs to be by Grand Total (Of columns $$, $$$, $$$$, etc). I used a VLookup to grab the grand totals but had to change it to each spreadsheet name (about 200 spreadsheets total). I have many more to do and will try your suggestions, if you have any more I am more than happy to take them! Thank you so much for all your help...

Report •

January 15, 2010 at 13:28:39

Here is a quick and dirty Macro to sum all the subtotals in all worksheets (except the master sheet itself), and place them on the master under a heading - which is the name of the Worksheet tab.

You can change the destination row and first column used in these lines:
'set first column & row for results
intResultCol = 1
intResultRow = 2

The subtotals must be in the same cells on each worksheet.
Change this line to match the cells containing the subtotals:
dblTotal = Application.WorksheetFunction.Sum(wsEach.Range("B501:E501"))

Private Sub TotalSubtotals ()
Dim wsEach As Worksheet
Dim strMasterName As String
Dim dblTotal As Double
Dim intResultCol As Integer
Dim intResultRow As Integer

'assume that the page this is called from is the Master
strMasterName = ActiveSheet.Name

'set first column & row for results
intResultCol = 1
intResultRow = 2

'get totals from every page except the master
For Each wsEach In ActiveWorkbook.Worksheets()
    'exclude the Master sheet from summing
    If wsEach.Name <> strMasterName Then
        'get total of subtotals
        dblTotal = Application.WorksheetFunction.Sum(wsEach.Range("B501:E501"))
        'put name of sheet in result row
        Worksheets(strMasterName).Range("A1"). _
            Offset(intResultRow, intResultCol).Value = wsEach.Name
        'put total on next row, same column
        Worksheets(strMasterName).Range("A1"). _
            Offset(intResultRow + 1, intResultCol).Value = dblTotal
        'next column
        intResultCol = intResultCol + 1
    End If
Next wsEach
End Sub

This code can be triggered by a command button on the master worksheet, or you could use the On Change Event, but that would require some changes to the code.

Excel has 'Events' which are triggered by certain actions such as recalculation of a worksheet, activating a worksheet or the user saving a workbook etc. etc.

These events can be used to run Macros. There is an overhead to this especially anything that runs with every recalculation for example.


Report •

Related Solutions

January 15, 2010 at 13:44:46
Brilliant! This Macro will help me with a different project I am working on, it's pretty remarkable what you can do!

Unfortunately, it doesn't work on the current project because the grand totals do no all total to the same cells in each sheet. Some sheets have 45 rows while others only have 5, thus the subtotals jump around accordingly... See anyway around that? Maybe there's a setting on the Auto Subtotal feature that allows me to tell it where to put the Grand Total on each sheet. I'll look around for that...

In the meantime, thanks again for the other Macro, it is most helpful!

Report •

January 15, 2010 at 15:05:18

You should be able to have all the totals on the same row. It doesn't matter if there are different numbers of empty cells between the actual data and the cell with the data.

Another option is to keep your subtotals where you have them, row 6 or 66 - it doesn't matter, then on say row 501 create a link to the subtotal in the same column. E.g., in B501 have =B6, C501 = C6 etc.
On the next sheet B501 is =B66, C501 =C66 etc.

That way you can use the macro.


Report •

January 15, 2010 at 15:56:48
Alright, I took off the subtotals to try and get this to work, maybe you can help me make this simpler...
Still have 60 sheets
Summary sheet (Master)

I need to pull cells K750:Q750 and put them on the summary sheet...
Sheet 1 (assume all data starts at K750)
1101 $100 $200 $5000 $$$ etc...
Sheet 2
1102 $200 $500 $600 $$$$ etc...
Sheet 3
1103 $400 $900 $2500 $$ etc....

Run marco to pull just those cells onto summary which will read:
1101 $100 $200 $5000 $$$ etc...
1102 $200 $500 $600 $$$$ etc...
1103 $400 $900 $2500 $$ etc....

Ugh, sorry for the trouble, I think this way is easier than trying to total sub-totals

Report •

January 16, 2010 at 07:18:05

This macro creates a table of sheet names with formulas linking the total cells.
Using your example this is what the output looks like for just K to M (the code does K to Q):

	A		B		C		D
1	Sheet name	Totals 1	Totals 2	Totals 3
2	1101		$100		$200		$5,000
3	1102		$200		$500		$600
4	1103		$400		$900		$2,500

Cell B2 contains ='1101'!K750
Cell D4 contains ='1103'!M750

You can change the code to link to more cells (columns) - its a simple for next loop, currently n = 1 to 7

As this macro only needs to be run when you add new worksheets, it doesn't need to be attached to a command button. Just put it in a standard module (not a class module), attached to the workbook. Then place the cursor anywhere inside the code, after the opening sub, and click f5, or use f8 to single step through it to see what is happeneing.

Option Explicit

Private Sub TotalsFormulas()
Dim wsEach As Worksheet
Dim intRowCount As Integer
Dim n As Integer

'set row offset
intRowCount = 0

With ActiveWorkbook
    'loop through all worksheets
    For Each wsEach In .Worksheets()
        'only get data if the worksheet is not the Master
        If wsEach.Name <> "Master" Then
            'copy worksheet name to next row - column A
            .Worksheets("Master").Range("A2").Offset(intRowCount, 0).Value = _
            'create formulas linking to cells K750 to Q750 on each sheet
            'starting at column B on the master sheet (uses n as column offset)
            For n = 1 To 7
                .Worksheets("Master").Range("A2").Offset(intRowCount, n).Formula = _
                "='" & wsEach.Name & "'!" & Chr(Asc("K") + n - 1) & "750"
            Next n
           'next row
            intRowCount = intRowCount + 1
        End If
    Next wsEach
End With
End Sub

If you just want a single total, this line replaces the For n - Next n loop

'create SUM() formula for cells K750:Q750 on each worksheet
 .Worksheets("Master").Range("A2").Offset(intRowCount, 1).Formula = _
          "=SUM('" & wsEach.Name & "'!K750:Q750)"

This places the following formula in cell B2 =SUM('1101'!K750:Q750) and so on for each worksheet

Note that the method of creating the column letters K to Q in the for - next loop will not work beyond Z. If at some time you extend your data beyond column Z, a different way of creating the column letters will be required, e.g., for column AA. In the SUM() version, just enter the cell address range.


Report •

January 18, 2010 at 08:24:11
I tried your code but am getting a run time error'9': Subscript out of range.
It highlights this line:
.Worksheets("Master").Range("A2").Offset(intRowCount, 0).Value = wsEach.Name

Do you know what I am doing wrong?

Report •

January 18, 2010 at 08:48:16

First of all the macro uses 'With ActiveWorkbook'. So make sure that the workbook that is active - the one in the main Excel window, with any cell selected, is the workbook with the Master worksheet.

If you have the correct workbook active, and it still fails, then it is possible that the name I used for your Master worksheet and the name you are using are not identical.

Do this:
In the code, highlight and copy the word Master in the line that is causing a problem.
Right-click and copy, (the name without the double quotes).
On the master worksheet, go to the name tab at the bottom and right click the tab and select Rename
The current name is highlighted in black - don't click on it - just use Ctrl + V (Control key plus uppercase V together) which will paste in the name. Then click on any cell.

If there was a difference, such as a space after the name, this should now be corrected.

If you still get an error at this line, place a breakpoint on the line before the one that gives the error (If wsEach.Name <> "Master" Then
Run the macro to the breakpoint, then hover the mouse over the word intRowCount and record the value. Also hover over wsEach.Name and record the name, and let me know what you see.


Report •

January 18, 2010 at 09:07:36
I copy and pasted only a few of my worksheets into a new file while I played around with your macros and forgot to change my summary tab back to "master." Easy fix! Thank you so much, this works like a dream and will save me an exceptional amount of time! Thank you thank you thank you!

Report •

January 18, 2010 at 10:52:49

Glad its working -

and thanks for the feedback



Report •

Ask Question