Sub-Totaling

T

tamxwell

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").Select
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
 
D

Dave Peterson

You have one suggestion at your post in .misc.
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").Select
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating Pivot Table with Macro using Multiple Sheets 0
Macros will not run with shortcut keys 2
Modify Recorded Macro 1
Option Box on user from to update workbook 6
MACRO Effeciency 3
variable number of rows. 3
Macro 3
VB Printing problem 0

Top