# 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 upSheet 1Type: \$\$Cost \$\$Depreciation \$\$ect...mme: \$2000.00 \$40 \$433.00ff: \$1500.00 \$35 \$510.00TOTAL: \$3500 \$75 \$943.00Sheet 2Type: \$\$Cost \$\$Depreciation \$\$ect...mme: \$3000.00 \$50 \$200.00ff: \$1500.00 \$45 \$510.00TOTAL: \$4500 \$95 \$710.00There 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.00Sheet2 4500 95 710.00

See More: Summary Sheet of subtotals from mult. sheets

#1
January 15, 2010 at 12:24:31
 Reply,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\$501Drag to the right to make '1101'!C\$501 and '1101'!D\$501Drag 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 XXYYUse 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 formulasI note that you refer to the SUBTOTAL() functionIs 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?Regards

Report •

#2
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 \$100MME \$15 \$120MME \$25 \$150FF \$100 \$1000FF \$100 \$5000HHHHHHYou 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 •

#3
January 15, 2010 at 13:28:39
 Hi,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 resultsintResultCol = 1intResultRow = 2The 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.Regards

Report •

Related Solutions

#4
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 •

#5
January 15, 2010 at 15:05:18
 Hi,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.Regards

Report •

#6
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 sheetsSummary sheet (Master)I need to pull cells K750:Q750 and put them on the summary sheet...Example:Sheet 1 (assume all data starts at K750)1101 \$100 \$200 \$5000 \$\$\$ etc...Sheet 21102 \$200 \$500 \$600 \$\$\$\$ etc...Sheet 31103 \$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 •

#7
January 16, 2010 at 07:18:05
 Hi,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'!K750Cell D4 contains ='1103'!M750You can change the code to link to more cells (columns) - its a simple for next loop, currently n = 1 to 7As 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 = _ wsEach.Name '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 worksheetNote 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.Regards

Report •

#8
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.NameDo you know what I am doing wrong?

Report •

#9
January 18, 2010 at 08:48:16
 Hi, 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 RenameThe 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.Regards

Report •

#10
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 •

#11
January 18, 2010 at 10:52:49
 Hi,Glad its working -and thanks for the feedbackRegardsHumar

Report •