| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Sub-Totaling
I have built a template for the Accounts Receivables department. This is for
tracking agings of money owed to us from the status of Current to 360 days past due. I built a Database to pull the info from our AS/400 system from which the Credit Managers (CM) export to Excel. The template Sub-Totals by Customer Number, and there might be 5 to 60 companies that it sub-totals. I have to sub by customer number, but when I do this it leaves out the Customer name, reference number, branch number, etc on the second tab. The CMs then have to go to the third tab to copy the rest of the info and paste it in the second tab. A big pain. I have written some VB in another project to due these calculations , but these scripts give all the info on all the Cms not just individually that I need. I would like to just export the info to the desktop and then use the subtotal function with all the info on the second tab. Is these possible? I will list the fields names and also list my VB Thanks Todd co-number,divn-number,cust-number,cust-name,TranType,ref-number,as-of-date, due-date,item-amount,Today,DSO, Current, 1-30, 31-60, 61-90, 91-180, 181-360, 360+, CA,CM, TOTAL RESERVES Sub TOTALRESERVETEMPLATE() ' ' TOTALRESERVETEMPLATE Macro ' 7/25/2005 by tmaxwell ' ' Cells.Select Cells.EntireColumn.AutoFit Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("C:C").Select With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With ActiveWindow.SmallScroll Down:=43 ActiveWindow.LargeScroll Down:=1 ActiveWindow.SmallScroll Down:=57 ActiveWindow.LargeScroll Down:=3 ActiveWindow.SmallScroll Down:=1 ActiveWindow.LargeScroll Down:=47 ActiveWindow.SmallScroll Down:=-59 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 7 Columns("U:U").Select Selection.Delete Shift:=xlToLeft Selection.ColumnWidth = 15.86 Range("I:I,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,U:U").Selec t Range("U1").Activate Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" ActiveWindow.SmallScroll ToRight:=2 Range("U2").Select ActiveCell.FormulaR1C1 = _ "=SUM((RC[-3]))=(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)" Range("U2").Select ActiveCell.FormulaR1C1 = _ "=SUM((RC[-3]))+R[2]C(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)" Range("U2").Select ActiveCell.FormulaR1C1 = _ "=SUM((RC[-3]))+(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)" Range("U2").Select Selection.Copy ActiveWindow.ScrollRow = 1711 ActiveWindow.SmallScroll Down:=1 ActiveWindow.LargeScroll Down:=130 ActiveWindow.SmallScroll Down:=-2 ActiveWindow.LargeScroll Down:=-1 ActiveWindow.SmallScroll Down:=0 ActiveWindow.LargeScroll Down:=2 ActiveWindow.SmallScroll Down:=-1 ActiveWindow.LargeScroll Down:=-2 ActiveWindow.SmallScroll Down:=7 Range("U2:U6001").Select Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.ScrollColumn = 1 Cells.Select Application.CutCopyMode = False Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(9, 13, 14, _ 15, 16, 17, 18, 21), Replace:=True, PageBreaks:=False, SummaryBelowData:=True Columns("C:C").Select ActiveSheet.Outline.ShowLevels RowLevels:=2 Columns("C:C").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollRow = 6004 Range("A1:U6034").Select Selection.Sort Key1:=Range("U2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 1 Columns("D:H").Select Selection.EntireColumn.Hidden = True End Sub |
|
#2
|
|||
|
|||
|
Sub-Totaling
You may want to consider using a pivottable to do your summary.
After you get it working manually, you can toss that version and record a macro when you do it for real. If you want to read more about pivottables... Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx tamxwell wrote: I have built a template for the Accounts Receivables department. This is for tracking agings of money owed to us from the status of Current to 360 days past due. I built a Database to pull the info from our AS/400 system from which the Credit Managers (CM) export to Excel. The template Sub-Totals by Customer Number, and there might be 5 to 60 companies that it sub-totals. I have to sub by customer number, but when I do this it leaves out the Customer name, reference number, branch number, etc on the second tab. The CMs then have to go to the third tab to copy the rest of the info and paste it in the second tab. A big pain. I have written some VB in another project to due these calculations , but these scripts give all the info on all the Cms not just individually that I need. I would like to just export the info to the desktop and then use the subtotal function with all the info on the second tab. Is these possible? I will list the fields names and also list my VB Thanks Todd co-number,divn-number,cust-number,cust-name,TranType,ref-number,as-of-date, due-date,item-amount,Today,DSO, Current, 1-30, 31-60, 61-90, 91-180, 181-360, 360+, CA,CM, TOTAL RESERVES Sub TOTALRESERVETEMPLATE() ' ' TOTALRESERVETEMPLATE Macro ' 7/25/2005 by tmaxwell ' ' Cells.Select Cells.EntireColumn.AutoFit Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("C:C").Select With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With ActiveWindow.SmallScroll Down:=43 ActiveWindow.LargeScroll Down:=1 ActiveWindow.SmallScroll Down:=57 ActiveWindow.LargeScroll Down:=3 ActiveWindow.SmallScroll Down:=1 ActiveWindow.LargeScroll Down:=47 ActiveWindow.SmallScroll Down:=-59 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 7 Columns("U:U").Select Selection.Delete Shift:=xlToLeft Selection.ColumnWidth = 15.86 Range("I:I,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,U:U").Selec t Range("U1").Activate Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" ActiveWindow.SmallScroll ToRight:=2 Range("U2").Select ActiveCell.FormulaR1C1 = _ "=SUM((RC[-3]))=(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)" Range("U2").Select ActiveCell.FormulaR1C1 = _ "=SUM((RC[-3]))+R[2]C(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)" Range("U2").Select ActiveCell.FormulaR1C1 = _ "=SUM((RC[-3]))+(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)" Range("U2").Select Selection.Copy ActiveWindow.ScrollRow = 1711 ActiveWindow.SmallScroll Down:=1 ActiveWindow.LargeScroll Down:=130 ActiveWindow.SmallScroll Down:=-2 ActiveWindow.LargeScroll Down:=-1 ActiveWindow.SmallScroll Down:=0 ActiveWindow.LargeScroll Down:=2 ActiveWindow.SmallScroll Down:=-1 ActiveWindow.LargeScroll Down:=-2 ActiveWindow.SmallScroll Down:=7 Range("U2:U6001").Select Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.ScrollColumn = 1 Cells.Select Application.CutCopyMode = False Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(9, 13, 14, _ 15, 16, 17, 18, 21), Replace:=True, PageBreaks:=False, SummaryBelowData:=True Columns("C:C").Select ActiveSheet.Outline.ShowLevels RowLevels:=2 Columns("C:C").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollRow = 6004 Range("A1:U6034").Select Selection.Sort Key1:=Range("U2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 1 Columns("D:H").Select Selection.EntireColumn.Hidden = True End Sub -- Dave Peterson |
|
#3
|
|||
|
|||
|
Sub-Totaling
Thanks for resonding. Because I have Percentages reductions of 100, 50, 20,
10 on dollars collected when they account reach 60, 90, 180, 360 days, getting all the calculations in the right place is a liitle like trading one old horse for another. I have a number of P-tables doing various things, I take a look. Thanks Dave "Dave Peterson" wrote: You may want to consider using a pivottable to do your summary. After you get it working manually, you can toss that version and record a macro when you do it for real. If you want to read more about pivottables... Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx tamxwell wrote: I have built a template for the Accounts Receivables department. This is for tracking agings of money owed to us from the status of Current to 360 days past due. I built a Database to pull the info from our AS/400 system from which the Credit Managers (CM) export to Excel. The template Sub-Totals by Customer Number, and there might be 5 to 60 companies that it sub-totals. I have to sub by customer number, but when I do this it leaves out the Customer name, reference number, branch number, etc on the second tab. The CM€„¢s then have to go to the third tab to copy the rest of the info and paste it in the second tab. A big pain. I have written some VB in another project to due these calculations , but these scripts give all the info on all the Cm€„¢s not just individually that I need. I would like to just export the info to the desktop and then use the subtotal function with all the info on the second tab. Is these possible? I will list the fields names and also list my VB Thanks Todd co-number,divn-number,cust-number,cust-name,TranType,ref-number,as-of-date, due-date,item-amount,Today,DSO, Current, 1-30, 31-60, 61-90, 91-180, 181-360, 360+, CA,CM, TOTAL RESERVES Sub TOTALRESERVETEMPLATE() ' ' TOTALRESERVETEMPLATE Macro ' 7/25/2005 by tmaxwell ' ' Cells.Select Cells.EntireColumn.AutoFit Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("C:C").Select With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With ActiveWindow.SmallScroll Down:=43 ActiveWindow.LargeScroll Down:=1 ActiveWindow.SmallScroll Down:=57 ActiveWindow.LargeScroll Down:=3 ActiveWindow.SmallScroll Down:=1 ActiveWindow.LargeScroll Down:=47 ActiveWindow.SmallScroll Down:=-59 ActiveWindow.ScrollRow = 1 ActiveWindow.ScrollColumn = 7 Columns("U:U").Select Selection.Delete Shift:=xlToLeft Selection.ColumnWidth = 15.86 Range("I:I,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,U:U").Selec t Range("U1").Activate Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)" ActiveWindow.SmallScroll ToRight:=2 Range("U2").Select ActiveCell.FormulaR1C1 = _ "=SUM((RC[-3]))=(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)" Range("U2").Select ActiveCell.FormulaR1C1 = _ "=SUM((RC[-3]))+R[2]C(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)" Range("U2").Select ActiveCell.FormulaR1C1 = _ "=SUM((RC[-3]))+(RC[-4]*0.5)+(RC[-5]*0.2)+(RC[-6]*0.1)" Range("U2").Select Selection.Copy ActiveWindow.ScrollRow = 1711 ActiveWindow.SmallScroll Down:=1 ActiveWindow.LargeScroll Down:=130 ActiveWindow.SmallScroll Down:=-2 ActiveWindow.LargeScroll Down:=-1 ActiveWindow.SmallScroll Down:=0 ActiveWindow.LargeScroll Down:=2 ActiveWindow.SmallScroll Down:=-1 ActiveWindow.LargeScroll Down:=-2 ActiveWindow.SmallScroll Down:=7 Range("U2:U6001").Select Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveWindow.ScrollColumn = 1 Cells.Select Application.CutCopyMode = False Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(9, 13, 14, _ 15, 16, 17, 18, 21), Replace:=True, PageBreaks:=False, SummaryBelowData:=True Columns("C:C").Select ActiveSheet.Outline.ShowLevels RowLevels:=2 Columns("C:C").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollRow = 6004 Range("A1:U6034").Select Selection.Sort Key1:=Range("U2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 1 Columns("D:H").Select Selection.EntireColumn.Hidden = True End Sub -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Totaling cells from p.c. worksheets to a cell on a server workshee | Excel Discussion (Misc queries) | |||
| Totaling Hours in Standard Format | Excel Worksheet Functions | |||
| totaling figures in colum | Excel Worksheet Functions | |||
| Having trouble totaling columns in excel 2003, always worked b4 | Excel Worksheet Functions | |||
| totaling times past 24 hours | Excel Worksheet Functions | |||
Linear Mode
