ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Sub-Totaling (https://www.excelbanter.com/excel-worksheet-functions/54624-sub-totaling.html)

tamxwell
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

Dave Peterson
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


All times are GMT +1. The time now is 11:59 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com