| 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 have one suggestion at your post in .misc.
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 |
| 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
